**کد شما باید روی PostgreSQL قابل اجرا باشد.**
----------
در این سوال، بخشی از پایگاه داده مربوط به اطلاعات پرواز کشور روسیه در اختیار شما قرار گرفته است.
# جزئیات دیتابیس
داده‌های اولیه برای **تست نهایی** را از [این لینک](/problemset/assignments/4367/download_problem_initial_project/211020/) دانلود کنید.
<details class="yellow">
<summary>
**توضیحات در مورد داده‌های اولیه**
</summary>
در فایل `Travel.zip` فایلی به اسم `initial.sql` وجود دارد.
با اجرای این فایل، همه‌ی جداول و سطرهایی که برای **تست نهایی** مورد استفاده قرار می‌گیرد در پایگاه داده شما ایجاد می‌شود. پیش از اجرای این فایل، حتما پایگاه داده با اسم دلخواه ایجاد کنید و در ابتدای این فایل، کد `use YOUR_DB_NAME;` را وارد کنید.
</details>
<details class="grey">
<summary>
**توضیحات جداول دیتابیس**
</summary>
ساختار جداول به‌شرح زیر است:
**جدول `aircraft_data`**: از این جدول برای نگه‌داری اطلاعات هواپیما‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------:|:---:|:----:|
|`aircraft_code`|`character varying(8)`|کد هواپیما|
|`model`|`character varying(64)`|مدل هواپیما|
|`range`|`integer`|برد هواپیما|
**جدول `airports_data`**: از این جدول برای نگه‌داری اطلاعات فرودگاهها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------:|:--:|:----:|
|`airport_code`|`character varying(4)`|کد فرودگاه|
|`airport_name`|`json`|نام فرودگاه با فرمت JSON|
|`city`|`json`|شهری که فرودگاه در آن قرار دارد با فرمت JSON|
|`timezone`|`character varying(32)`|منطقه زمانی فرودگاه|
**جدول `boarding_passes`**: از این جدول برای نگه‌داری اطلاعات کارت‌های پرواز استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------:|:--:|:----:|
|`ticket_no`|`character varying(8)`|شماره بلیط|
|`flight_id`|`integer`|شناسه پرواز|
|`boarding_no`|`integer`|شماره کارت پرواز|
|`seat_no`|`character varying(4)`|شماره صندلی|
**جدول `flights`**: از این جدول برای نگه‌داری اطلاعات پروازها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------:|:--:|:----:|
|`flight_id`|`integer`|شناسه پرواز|
|`flight_no`|`character varying(8)`|شماره پرواز|
|`scheduled_departure`|`character varying(32)`|زمان حرکت برنامه ریزی شده|
|`scheduled_arrival`|`character varying(32)`|زمان ورود برنامه ریزی شده|
|`departure_airport`|`character varying(4)`|کد فرودگاه مبدا|
|`arrival_airport`|`character varying(4)`|کد فرودگاه مقصد|
|`status`|`character varying(16)`|وضعیت پرواز|
|`aircraft_code`|`character varying(4)`|کد هواپیما|
**جدول `seats`**: از این جدول برای نگه‌داری اطلاعات صندلی‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------:|:--:|:----:|
|`aircraft_code`|`character varying(4)`|کد هواپیما|
|`seat_no`|`character varying(4)`|شماره صندلی|
|`fare_conditions`|`character varying(8)`|کلاس صندلی هواپیما|
**جدول `ticket_flights`**: از این جدول برای نگه‌داری اطلاعات بلیط‌های هر پرواز استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------:|:--:|:----:|
|`ticket_no`|`character varying(16)`|شماره بلیط|
|`flight_id`|`integer`|شناسه پرواز|
|`fare_conditions`|`character varying(8) `|کلاس بلیط هواپیما|
|`amount`|`integer`|مبلغ بلیط|
**جدول `tickets`**: از این جدول برای نگه‌داری اطلاعات بلیط‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------:|:--:|:----:|
|`ticket_no`|`character varying(16)`|شماره بلیط|
|`book_ref`|`character varying(8)`|کد مرجع رزرو|
|`passenger_id`|`character varying(16)`|شناسه مشتری|
</details>
# مطلوبات
۱. تعداد صندلی‌های هر هواپیما به تفکیک کلاس آن‌ها
<details class="blue">
<summary>
*توضیحات مربوط به کوئری اول*
</summary>
خروجی این کوئری شامل سه ستون با نام‌های `aircraft_code` و `fare_conditions` و `count` است که به ترتیب کد هواپیما، نوع کلاس صندلی و تعداد صندلی‌های موجود در هواپیمای مذکور با کلاس مشخص شده است. در نهایت جدول، باید بر اساس ستون `aircraft_code` به صورت صعودی و سپس براساس `count` به صورت نزولی مرتب شود و در صورت برابر بودن این دو ستون بر اساس ستون `fare_conditions` به صورت صعودی مرتب شود.
۴ سطر اول خروجی شما باید به شکل زیر باشد:
| aircraft_code| fare_conditions| count|
|:---:|:---:|:---:|
| 319 | Economy | 96 |
| 319 | Business | 20 |
| 320 | Economy | 120 |
| 320 | Business | 20 |
</details>
۲. مجموع تعداد پروازهای کنسل شده در روز‌های مختلف هفته
</details>
<details class="blue">
<summary>
*توضیحات مربوط به کوئری دوم*
</summary>
خروجی این جدول شامل دو ستون با نام‌های `day` و `count` است که به ترتیب شامل روز‌های هفته و تعداد پروازهای کنسل شده در آن روز هستند. جدول نهایی باید بر اساس ستون `count` به صورت نزولی و سپس بر اساس ستون `day` به صورت صعودی مرتب شود.
۲ سطر اول خروجی شما باید به شکل زیر باشد
| day | count |
|:---:|:---:|
| Wednesday | 2 |
| Thursday | 1 |
</details>
۳. رتبه‌بندی تعداد مسافر جابه‌جا شده در فرودگاهها توسط هواپیما‌های مختلف
<details class="blue">
<summary>
*توضیحات مربوط به کوئری سوم*
</summary>
جدول خروجی شامل چهار ستون با نام‌های `airport` و `aircraft_model` و `count`
و `rank` است که به ترتیب شامل نام فرودگاه، مدل هواپیما، تعداد مسافرانی که توسط این هواپیما یا به این فرودگاه آمدند یا از این فرودگاه رفتند ( **طبیعتا پروازهای کنسل شده نباید حساب شوند**)، است و رتبه این هواپیما در بین تمام هواپیما‌ها که در این فرودگاه تردد کردند. دقت کنید که رتبه‌بندی بر اساس تعداد دفعات تردد (ستون `count`) به این صورت محاسبه می‌شود که اگر دو هواپیما با بیشترین `count`، یکسان بودند هردو رتبه یک می‌شوند و هواپیما بعدی رتبه‌اش دو می‌شود. جدول نهایی، باید به ترتیب بر اساس ستون‌های `airport` و `rank` و `aircraft_model` به صورت صعودی مرتب باشد.
**توجه ۱: لازم به ذکر است برای نام شهر‌ها، باید از نام انگلیسی که در JSON مربوطه وجود دارد، استفاده کنید.**
۴ سطر اول خروجی شما باید به شکل زیر باشد.
| airport | aircraft_model | count | rank |
|:---:|:---:|:---:|:---:|
| Abakan Airport | Cessna 208 Caravan | 435 | 1 |
| Abakan Airport | Airbus A319-100 | 405 | 2 |
| Anapa Vityazevo Airport | Boeing 737-300 | 2765 | 1 |
| Anapa Vityazevo Airport | Sukhoi Superjet-100 | 2017 | 2 |
</details>
۴. فاصله زمانی شهر‌های مختلف
<details class="blue">
<summary>
*توضیحات مربوط به کوئری چهارم*
</summary>
جدول خروجی شامل سه ستون با نام‌های `city1` و `city2` و `time_dist` است که به ترتیب شهر‌های دو سر مسیر و فاصله زمانی شهر‌هاست. جدول نهایی باید بر اساس ستون `time_dist` به صورت نزولی و سپس بر اساس `city1` و `city2` به صورت صعودی مرتب باشد. توجه کنید که هر زوج شهر باید یک بار تکرار شود و همچنین از لحاظ ترتیب الفبایی باید`city1` نسبت به `city2` مقدم باشد.
**توجه: فاصله زمانی شهر‌ها برابر است با میانگین فاصله زمانی بین فرودگاه‌های آن دو شهر و فاصله زمانی دو فرودگاه برابر است با میانگین اختلاف زمان حرکت و ورود برنامه‌ریزی شده در بلیط‌های مختلفی که برای این دو فرودگاه وجود دارد (دقت کنید که بلیط‌های کنسل شده هم باید حساب شوند):**
$$ time\ distance(c_1, c_2) = Average_{a_1\in c_1, a_2\in c_2}\ \{time\ distance(a_1, a_2)\} $$
$$ time\ distance(a_1, a_2) = Average_{f\in flights}\ \{f.scheduled\_arrival - f.scheduled\_departure\} $$
۳ سطر اول خروجی شما باید به شکل زیر باشد.
| city1 | city2 | time_dist |
|:---:|:---:|:---:|
| Moscow | Petropavlovsk | 530 |
| Moscow | Yuzhno-Sakhalinsk | 527.5 |
| Magadan | Mineralnye Vody | 525 |
</details>
# روش پیاده‌سازی
در یک فایل با نام `code.sql` کد خود را قرار دهید و آن را فشرده (`zip` ) کنید و در سایت بارگذاری نمایید.
کد شما باید به صورت زیر باشد(نام فایل zip مهم نیست).
```sql
-- Section1
Your first query here
-- Section2
Your second query here
-- Section3
Your third query here
-- Section4
Your fourth query here
```