**کد شما باید روی PostgreSQL قابل اجرا باشد.**
---
**پوریا** *(Pouria)* از **مهندسین نرمافزار گردنکلفت دوتانت** *(DotaNet)،* به تازگی به عنوان **مدیر آموزش مسابقات مجموعه کوئرا** در این مجموع شروع به کار کرده است. او که به دلیل **محاسبات** و **کنتوراندازیهای خفن مالی** به **پولیا** *(Poolia)* معروف شده است در اولین برخوردش در کوئرا با **دوین** *(Dwin)* متوجه شده است که دوین، **از پرخرجترین کارکنان کوئراست!** او که به گفتهی مسئولین مالی کوئرا، همواره **به اندازه یک تیم کامل از سایر کارکنان کوئرا،** حقوق دریافت میکند؛ برای **طراحی کانتست جدید** مجموعه [**فناوا** *(Fanava)*](https://fanava.net/) نیز درخواست وجوهات سنگین از پوریا کرده است. از آنجایی که **پوریا** مثل همیشه درگیر برگزاری تعداد زیادی از مسابقات مهندسی نرمافزارانه است، از شما در این سوال برای **کنتور اندازی** بر اساس **دیتاست مشخصی از مجموعه مسابقات برنامهنویسی برگزار شده در گذشته شامل تیمهای شرکتکننده از سراسر جهان،** کمک میخواهد **تا با دستی پر و آمارهایی جذاب به مقابله با دوین پرخرج برود...**
# **جزئیات پایگاهداده**
**دیتاستی** که در این سوال به آن میپردازیم، **متشکل از اطلاعات یک پلتفرم مسابقات برنامهنویسی** است. این دیتابیس شامل اطلاعات شرکتکنندگان، مسابقات، سوالات، ارسالها، تیمها، دستاوردها و تاریخچه امتیازات میباشد.
**دادههای اولیه** را از [این لینک](/contest/assignments/98478/download_problem_initial_project/330235/) دانلود کنید.
<details class="yellow">
<summary>**ایمپورت کردن دادههای اولیه**</summary>
از **نصب بودن** *PostgreSQL* روی سیستم خود اطمینان حاصل کنید. برای ایمپورت کردن دادههای اولیه میتوانید از یکی از **دو روش زیر** اقدام کنید:
## **روش اول: استفاده از** *CLI*
با استفاده از *CLI* **دستور** زیر را وارد کنید تا دادههای اولیه **ایمپورت** شوند:
```bash terminal terminal
psql -U postgres -f /path/to/initial.sql
```
- که در این دستور مسیر **فایل** `initial.sql` را به صورت **مطلق** یا **نسبی** میتوانید **آدرسدهی** کنید.
## **روش دوم: استفاده از** *GUI*
اگر *GUI* را ترجیح میدهید، پس از نصب **دیتاگریپ** و اتصال به *PostgreSQL* با **یوزر** *postgres،* باید روی **دیتاسورس** و **کانکشن** *postgres* راستکلیک کنید و از منوی *SQL Scripts،* گزینهی *Run SQL Script* را انتخاب کنید. سپس **فایل** `initial.sql` را پیدا و تایید کنید. در انتها روی *Run* کلیک کنید تا اسکریپت اجرا شود و دادهها وارد دیتابیس شوند.
</details>
<details class="grey">
<summary>**توضیحات جداول دیتاست**</summary>
## **جدول** `country`
این جدول اطلاعات مربوط به **کشورها** را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا برای کشور *(کلید اصلی)* |
| `name` | `varchar(100)` | نام کشور |
| `code` | `varchar(10)` | کد کشور |
| `continent` | `varchar(50)` | قاره |
| `population` | `bigint` | جمعیت کشور |
| `gdp_per_capita` | `decimal(12,2)` | **تولید ناخالص** داخلی سرانه |
## **جدول** `university`
این جدول اطلاعات مربوط به **دانشگاهها** را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی **یکتا** برای دانشگاه *(کلید اصلی)* |
| `name` | `varchar(200)` | نام دانشگاه |
| `country_id` | `integer` | شناسهی کشور مرتبط (کلید خارجی به جدول `country`) |
| `ranking` | `integer` | رتبهی جهانی دانشگاه |
| `founded_year` | `integer` | سال تاسیس |
| `student_count` | `integer` | تعداد دانشجویان |
| `is_public` | `boolean` | دانشگاه دولتی یا خصوصی |
## **جدول** `team`
این جدول اطلاعات مربوط به **تیمهای برنامهنویسی** را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا برای تیم *(کلید اصلی)* |
| `name` | `varchar(100)` | نام تیم |
| `founded_year` | `integer` | سال تاسیس |
| `is_active` | `boolean` | وضعیت فعال بودن تیم |
| `motto` | `varchar(500)` | شعار تیم |
| `max_members` | `integer` | حداکثر تعداد اعضا |
| `website` | `varchar(200)` | آدرس وبسایت |
## **جدول** `contestant`
این جدول اطلاعات مربوط به **شرکتکنندگان** *(برنامهنویسان)* را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا برای شرکتکننده *(کلید اصلی)* |
| `username` | `varchar(50)` | نام کاربری *(یکتا)* |
| `first_name` | `varchar(100)` | نام |
| `last_name` | `varchar(100)` | نام خانوادگی |
| `email` | `varchar(200)` | ایمیل |
| `rating` | `integer` | امتیاز فعلی *(پیشفرض 1200)* |
| `max_rating` | `integer` | بیشترین امتیاز کسب شده |
| `country_id` | `integer` | شناسهی کشور (کلید خارجی به جدول `country`) |
| `university_id` | `integer` | شناسهی دانشگاه (کلید خارجی به جدول `university`) |
| `registration_date` | `date` | تاریخ ثبتنام |
| `last_online` | `timestamp` | آخرین زمان آنلاین بودن |
| `is_admin` | `boolean` | آیا ادمین است |
| `contribution` | `integer` | امتیاز مشارکت |
| `friend_count` | `integer` | تعداد دوستان |
## **جدول** `team_membership`
این جدول رابطهی **چند به چند** بین **شرکتکنندگان** و **تیمها** را مدیریت میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا *(کلید اصلی)* |
| `contestant_id` | `integer` | شناسهی شرکتکننده *(کلید خارجی)* |
| `team_id` | `integer` | شناسهی تیم *(کلید خارجی)* |
| `joined_date` | `date` | تاریخ پیوستن |
| `left_date` | `date` | تاریخ خروج *(در صورت خروج)* |
| `role` | `varchar(50)` | نقش در تیم *(leader, co-leader, member)* |
| `is_active` | `boolean` | آیا عضویت فعال است |
## **جدول** `achievement`
این جدول اطلاعات مربوط به **دستاوردها** و **نشانها** را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا *(کلید اصلی)* |
| `name` | `varchar(100)` | نام دستاورد |
| `description` | `text` | توضیحات |
| `badge_color` | `varchar(20)` | رنگ نشان *(gold, silver, bronze, platinum)* |
| `points` | `integer` | امتیاز دستاورد |
| `category` | `varchar(50)` | دستهبندی *(contest, problem, contribution, special)* |
## **جدول** `contestant_achievement`
این جدول **دستاوردهای کسب شده** توسط هر شرکتکننده را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا *(کلید اصلی)* |
| `contestant_id` | `integer` | شناسهی شرکتکننده *(کلید خارجی)* |
| `achievement_id` | `integer` | شناسهی دستاورد *(کلید خارجی)* |
| `earned_date` | `timestamp` | زمان کسب دستاورد |
| `contest_id` | `integer` | شناسهی مسابقه مرتبط *(اختیاری)* |
## **جدول** `contest`
این جدول اطلاعات مربوط به **مسابقات برنامهنویسی** را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا برای مسابقه *(کلید اصلی)* |
| `name` | `varchar(200)` | نام مسابقه |
| `contest_type` | `varchar(50)` | نوع مسابقه *(Educational, Div1, Div2, Global, Championship)* |
| `division` | `integer` | دستهبندی *(1, 2, 3, 4 یا NULL)* |
| `start_time` | `timestamp` | زمان شروع مسابقه |
| `duration_minutes` | `integer` | مدت زمان مسابقه به دقیقه |
| `max_participants` | `integer` | حداکثر تعداد شرکتکنندگان |
| `is_rated` | `boolean` | آیا مسابقه امتیازدهی میشود |
| `is_official` | `boolean` | آیا مسابقه رسمی است |
| `difficulty_rating` | `decimal(4,2)` | سطح دشواری میانگین *(1.0-10.0)* |
| `prize_pool` | `decimal(10,2)` | مجموع جوایز |
| `sponsor` | `varchar(200)` | اسپانسر |
| `created_by` | `integer` | سازندهی مسابقه (کلید خارجی به `contestant`) |
## **جدول** `contest_registration`
این جدول **اطلاعات ثبتنام** و **نتایج شرکتکنندگان** در هر مسابقه را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا *(کلید اصلی)* |
| `contestant_id` | `integer` | شناسهی شرکتکننده *(کلید خارجی)* |
| `contest_id` | `integer` | شناسهی مسابقه *(کلید خارجی)* |
| `registration_time` | `timestamp` | زمان ثبتنام |
| `is_virtual` | `boolean` | آیا شرکت مجازی است |
| `final_rank` | `integer` | رتبهی نهایی |
| `rating_change` | `integer` | تغییر امتیاز |
| `new_rating` | `integer` | امتیاز جدید |
| `problems_solved` | `integer` | تعداد سوالات حل شده |
| `penalty_time` | `integer` | زمان جریمه |
## **جدول** `tag`
این جدول **برچسبهای دستهبندی سوالات** را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا *(کلید اصلی)* |
| `name` | `varchar(100)` | نام برچسب |
| `description` | `text` | توضیحات |
| `difficulty_weight` | `decimal(3,2)` | وزن سختی |
## **جدول** `problem`
این جدول **اطلاعات مربوط به سوالات مسابقات** را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا برای سوال *(کلید اصلی)* |
| `contest_id` | `integer` | شناسهی مسابقه (کلید خارجی به جدول `contest`) |
| `problem_index` | `varchar(5)` | شاخص سوال *(A, B, C, D1, D2, ...)* |
| `title` | `varchar(200)` | عنوان سوال |
| `difficulty` | `varchar(20)` | سطح دشواری *(Easy, Medium, Hard, Expert)* |
| `points` | `integer` | امتیاز سوال |
| `time_limit_ms` | `integer` | محدودیت زمانی به میلیثانیه |
| `memory_limit_mb` | `integer` | محدودیت حافظه به مگابایت |
| `solved_count` | `integer` | تعداد حلهای موفق |
| `attempted_count` | `integer` | تعداد تلاشها |
| `rating` | `integer` | سطح سختی سوال *(800-3500)* |
| `author_id` | `integer` | نویسندهی سوال (کلید خارجی به `contestant`) |
## **جدول** `problem_tag`
این جدول **رابطهی چند به چند** بین **سوالات** و **برچسبها** را ایجاد میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا *(کلید اصلی)* |
| `problem_id` | `integer` | شناسهی سوال *(کلید خارجی)* |
| `tag_id` | `integer` | شناسهی برچسب *(کلید خارجی)* |
## **جدول** `submission`
این جدول اطلاعات مربوط به **ارسالهای شرکتکنندگان برای سوالات** را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا برای ارسال *(کلید اصلی)* |
| `contestant_id` | `integer` | شناسهی شرکتکننده (کلید خارجی به جدول `contestant`) |
| `problem_id` | `integer` | شناسهی سوال (کلید خارجی به جدول `problem`) |
| `contest_id` | `integer` | شناسهی مسابقه (کلید خارجی به جدول `contest`) |
| `submit_time` | `timestamp` | زمان ارسال |
| `status` | `varchar(30)` | وضعیت ارسال |
| `execution_time_ms` | `integer` | زمان اجرا به میلیثانیه |
| `memory_kb` | `integer` | حافظهی مصرفی به کیلوبایت |
| `language` | `varchar(50)` | زبان برنامهنویسی |
| `code_length` | `integer` | طول کد |
| `test_cases_passed` | `integer` | تعداد تستکیسهای موفق |
| `total_test_cases` | `integer` | تعداد کل تستکیسها |
| `is_practice` | `boolean` | آیا ارسال تمرینی است |
## **جدول** `rating_history`
این جدول **تاریخچه تغییرات امتیاز شرکتکنندگان** را ذخیره میکند.
| **نام ستون** | **نوع داده** | **توضیحات** |
|----------|----------|---------|
| `id` | `integer` | شناسهی یکتا *(کلید اصلی)* |
| `contestant_id` | `integer` | شناسهی شرکتکننده *(کلید خارجی)* |
| `contest_id` | `integer` | شناسهی مسابقه *(کلید خارجی)* |
| `old_rating` | `integer` | امتیاز قبلی |
| `new_rating` | `integer` | امتیاز جدید |
| `rating_change` | `integer` | میزان تغییر |
| `rank_in_contest` | `integer` | رتبه در مسابقه |
| `recorded_at` | `timestamp` | زمان ثبت |
</details>
## **مطلوبات**
کوئریهایی بنویسید که خروجیهای مطلوب زیر را بهدست آورد. توجه کنید که هر کوئری نمرهای جداگانه دارد و اگر کوئری قسمتی را **نتوانستید** بنویسید، کوئریهایی که حل کردید را **بفرستید** و کوئری آن قسمت را **خالی** بگذارید.
1. برای **هر شرکتکننده** که **امتیاز فعلی** او **بیشتر از میانگین** امتیاز **شرکتکنندگان قارهی خودش** است، اطلاعات **نام کاربری، نام کامل** (`first_name` و `last_name` با فاصله ترکیب شوند)، **امتیاز فعلی، بیشترین امتیاز، نام قاره، نام دانشگاه** (در صورت **نداشتن** دانشگاه، عبارت `Self-taught` نمایش داده شود) و همچنین یک **ستون** به **نام** `rating_tier` که بر اساس **امتیاز** مشخص کند:
- اگر **امتیاز** >= `3000` باشد: `Legendary`
- اگر **امتیاز** >= `2400` باشد: `Grandmaster`
- اگر **امتیاز** >= `1900` باشد: `Expert`
- **در غیر این صورت:** `Specialist`
نتایج باید **به ترتیب نزولی امتیاز** مرتب شوند و در صورت **برابری** امتیاز، **به ترتیب صعودی آیدی شرکتکننده.**
<details class="blue">
<summary>*نمونه خروجی کوئری اول*</summary>
| username | full_name | rating | max_rating | continent | university_name | rating_tier |
|----------|-----------|--------|------------|-----------|-----------------|-------------|
| tourist | Gennady Korotkevich | 3850 | 3979 | Europe | ITMO University | Legendary |
| petr | Petr Mitrichev | 3650 | 3750 | Europe | Moscow State University | Legendary |
| ecnerwala | Andrew He | 3500 | 3550 | North America | MIT | Legendary |
</details>
2. **برای هر تیم فعال،** ستونهای **نام تیم، تعداد اعضای فعال، میانگین امتیاز اعضای فعال** *(با دو رقم اعشار)،* **بالاترین امتیاز در بین اعضای فعال، نام کاربری عضوی که بالاترین امتیاز را دارد** *(در صورت تساوی، اولین نفر بر اساس آیدی)،* **مجموع جوایز کسب شده توسط اعضای تیم** (از جدول `contest_registration` **برای مسابقاتی که** `prize_pool > 0` دارند) **و همچنین یک ستون به نام** `team_level` که:
- اگر **میانگین امتیاز** >= `3000` باشد: `Elite`
- اگر **میانگین امتیاز** >= `2500` باشد: `Professional`
- اگر **میانگین امتیاز** >= `2000` باشد: `Advanced`
- **در غیر این صورت:** `Developing`
محاسبه و نمایش دهید. **فقط** تیمهایی را نمایش دهید که **حداقل ۲ عضو فعال** دارند. **نتایج باید به ترتیب نزولی میانگین امتیاز** مرتب شوند و در صورت برابری، **به ترتیب صعودی آیدی تیم.**
<details class="blue">
<summary>*نمونه خروجی کوئری دوم*</summary>
| team_name | active_members | avg_rating | max_rating | top_member | total_prize | team_level |
|-----------|----------------|------------|------------|------------|-------------|------------|
| Red Coders | 4 | 3412.50 | 3850 | tourist | (متغیر) | Elite |
</details>
3. **برای هر شرکتکننده** که در **حداقل ۲ مسابقه امتیازدهی شده** *(Rated)* شرکت کرده است، **اطلاعات نام کاربری، تعداد کل مسابقات شرکت کرده** *(Rated)،* **میانگین رتبه** در مسابقات، **بهترین رتبه** *(کمترین عدد)،* **مجموع تغییرات امتیاز** (`rating_change`)، رتبهی شرکتکننده **بر اساس مجموع تغییرات امتیاز در بین همهی شرکتکنندگان** *(با استفاده از DENSE_RANK و ترتیب نزولی)،* **تفاوت امتیاز فعلی با امتیاز اولیه** (بر اساس رابطه `rating` - `1200`) را برگردانید.
همچنین **فقط شرکتکنندگانی را نمایش دهید** که **مجموع تغییرات امتیاز آنها مثبت باشد.** نتایج باید **به ترتیب نزولی مجموع تغییرات امتیاز مرتب شوند** و در صورت برابری، **به ترتیب صعودی آیدی.**
<details class="blue">
<summary>*نمونه خروجی کوئری سوم*</summary>
| username | total_contests | avg_rank | best_rank | total_rating_change | performance_rank | rating_improvement |
|----------|----------------|----------|-----------|---------------------|------------------|-------------------|
| tourist | (متغیر) | (متغیر) | 1 | (متغیر) | 1 | 2650 |
</details>
4. **برای هر سوال که حداقل ۵ ارسال** *(Submission)* در **مسابقات اصلی** داشته (`is_practice` = `FALSE`)، **موارد شاخص سوال** (`problem_index`)، **عنوان سوال، نام مسابقه، نوع مسابقه، سطح دشواری سوال** (`difficulty`)، **امتیاز سختی سوال** (`rating`)، **تعداد کل ارسالها در مسابقه، تعداد ارسالهای موفق** *(Accepted)،* **نرخ موفقیت** *(درصد با دو رقم اعشار)،* **میانگین زمان اجرا برای ارسالهای موفق** *(با دو رقم اعشار)،* **یک ستون به نام** `actual_difficulty` که:
- اگر **نرخ موفقیت** < `20%` باشد: `Extremely Hard`
- اگر **نرخ موفقیت** < `40%` باشد: `Very Hard`
- اگر **نرخ موفقیت** < `60%` باشد: `Moderate`
- اگر **نرخ موفقیت** < `80%` باشد: `Easy`
- **در غیر این صورت:** `Very Easy`
- **یک ستون به نام** `difficulty_match` که **اگر سطح دشواری تعریف شده** با `actual_difficulty` **مطابقت داشته باشد** `Match` و **در غیر این صورت** `Mismatch` نمایش دهد.
نتایج باید **به ترتیب صعودی نرخ موفقیت** و **سپس به ترتیب صعودی آیدی سوال مرتب شوند.**
<details class="blue">
<summary>*نمونه خروجی کوئری چهارم*</summary>
| problem_index | problem_title | contest_name | contest_type | difficulty | problem_rating | total_submissions | accepted_count | success_rate | avg_execution_time | actual_difficulty | difficulty_match |
|---------------|---------------|--------------|--------------|------------|----------------|-------------------|----------------|--------------|-------------------|-------------------|------------------|
| E | Interactive Query | Codeforces Round 900 (Div. 1) | Div1 | Expert | 3200 | (متغیر) | (متغیر) | (متغیر) | (متغیر) | (متغیر) | (متغیر) |
</details>
# **فایل نهایی**
پس از پیادهسازی کوئریها، آن را در **فایل** `queries.sql` وارد کرده و سپس این فایل را **آپلود** کنید. کد شما باید به صورت زیر باشد:
```sql queries.sql sql
-- Section1
Your first query here
-- Section2
Your second query here
-- Section3
Your third query here
-- Section4
Your fourth query here
```