**کد شما باید روی 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 ```