کد شما باید روی PostgreSQL قابل اجرا باشد.
پوریا (Pouria) از مهندسین نرمافزار گردنکلفت دوتانت (DotaNet)، به تازگی به عنوان مدیر آموزش مسابقات مجموعه کوئرا در این مجموع شروع به کار کرده است. او که به دلیل محاسبات و کنتوراندازیهای خفن مالی به پولیا (Poolia) معروف شده است در اولین برخوردش در کوئرا با دوین (Dwin) متوجه شده است که دوین، از پرخرجترین کارکنان کوئراست! او که به گفتهی مسئولین مالی کوئرا، همواره به اندازه یک تیم کامل از سایر کارکنان کوئرا، حقوق دریافت میکند؛ برای طراحی کانتست جدید مجموعه فناوا (Fanava) نیز درخواست وجوهات سنگین از پوریا کرده است. از آنجایی که پوریا مثل همیشه درگیر برگزاری تعداد زیادی از مسابقات مهندسی نرمافزارانه است، از شما در این سوال برای کنتور اندازی بر اساس دیتاست مشخصی از مجموعه مسابقات برنامهنویسی برگزار شده در گذشته شامل تیمهای شرکتکننده از سراسر جهان، کمک میخواهد تا با دستی پر و آمارهایی جذاب به مقابله با دوین پرخرج برود...
جزئیات پایگاهداده
دیتاستی که در این سوال به آن میپردازیم، متشکل از اطلاعات یک پلتفرم مسابقات برنامهنویسی است. این دیتابیس شامل اطلاعات شرکتکنندگان، مسابقات، سوالات، ارسالها، تیمها، دستاوردها و تاریخچه امتیازات میباشد.
دادههای اولیه را از این لینک دانلود کنید.
ایمپورت کردن دادههای اولیه
از نصب بودن PostgreSQL روی سیستم خود اطمینان حاصل کنید. برای ایمپورت کردن دادههای اولیه میتوانید از یکی از دو روش زیر اقدام کنید:
روش اول: استفاده از CLI
با استفاده از CLI دستور زیر را وارد کنید تا دادههای اولیه ایمپورت شوند:
psql -U postgres -f /path/to/initial.sql
- که در این دستور مسیر فایل
initial.sqlرا به صورت مطلق یا نسبی میتوانید آدرسدهی کنید.
روش دوم: استفاده از GUI
اگر GUI را ترجیح میدهید، پس از نصب دیتاگریپ و اتصال به PostgreSQL با یوزر postgres، باید روی دیتاسورس و کانکشن postgres راستکلیک کنید و از منوی SQL Scripts، گزینهی Run SQL Script را انتخاب کنید. سپس فایل initial.sql را پیدا و تایید کنید. در انتها روی Run کلیک کنید تا اسکریپت اجرا شود و دادهها وارد دیتابیس شوند.
توضیحات جداول دیتاست
جدول 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 |
زمان ثبت |
مطلوبات
کوئریهایی بنویسید که خروجیهای مطلوب زیر را بهدست آورد. توجه کنید که هر کوئری نمرهای جداگانه دارد و اگر کوئری قسمتی را نتوانستید بنویسید، کوئریهایی که حل کردید را بفرستید و کوئری آن قسمت را خالی بگذارید.
- برای هر شرکتکننده که امتیاز فعلی او بیشتر از میانگین امتیاز شرکتکنندگان قارهی خودش است، اطلاعات نام کاربری، نام کامل (
first_nameوlast_nameبا فاصله ترکیب شوند)، امتیاز فعلی، بیشترین امتیاز، نام قاره، نام دانشگاه (در صورت نداشتن دانشگاه، عبارتSelf-taughtنمایش داده شود) و همچنین یک ستون به نامrating_tierکه بر اساس امتیاز مشخص کند:
- اگر امتیاز >=
3000باشد:Legendary - اگر امتیاز >=
2400باشد:Grandmaster - اگر امتیاز >=
1900باشد:Expert - در غیر این صورت:
Specialist
نتایج باید به ترتیب نزولی امتیاز مرتب شوند و در صورت برابری امتیاز، به ترتیب صعودی آیدی شرکتکننده.
نمونه خروجی کوئری اول
| 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 |
- برای هر تیم فعال، ستونهای نام تیم، تعداد اعضای فعال، میانگین امتیاز اعضای فعال (با دو رقم اعشار)، بالاترین امتیاز در بین اعضای فعال، نام کاربری عضوی که بالاترین امتیاز را دارد (در صورت تساوی، اولین نفر بر اساس آیدی)، مجموع جوایز کسب شده توسط اعضای تیم (از جدول
contest_registrationبرای مسابقاتی کهprize_pool > 0دارند) و همچنین یک ستون به نامteam_levelکه:
- اگر میانگین امتیاز >=
3000باشد:Elite - اگر میانگین امتیاز >=
2500باشد:Professional - اگر میانگین امتیاز >=
2000باشد:Advanced - در غیر این صورت:
Developing
محاسبه و نمایش دهید. فقط تیمهایی را نمایش دهید که حداقل ۲ عضو فعال دارند. نتایج باید به ترتیب نزولی میانگین امتیاز مرتب شوند و در صورت برابری، به ترتیب صعودی آیدی تیم.
نمونه خروجی کوئری دوم
| team_name | active_members | avg_rating | max_rating | top_member | total_prize | team_level |
|---|---|---|---|---|---|---|
| Red Coders | 4 | 3412.50 | 3850 | tourist | (متغیر) | Elite |
- برای هر شرکتکننده که در حداقل ۲ مسابقه امتیازدهی شده (Rated) شرکت کرده است، اطلاعات نام کاربری، تعداد کل مسابقات شرکت کرده (Rated)، میانگین رتبه در مسابقات، بهترین رتبه (کمترین عدد)، مجموع تغییرات امتیاز (
rating_change)، رتبهی شرکتکننده بر اساس مجموع تغییرات امتیاز در بین همهی شرکتکنندگان (با استفاده از DENSE_RANK و ترتیب نزولی)، تفاوت امتیاز فعلی با امتیاز اولیه (بر اساس رابطهrating-1200) را برگردانید.
همچنین فقط شرکتکنندگانی را نمایش دهید که مجموع تغییرات امتیاز آنها مثبت باشد. نتایج باید به ترتیب نزولی مجموع تغییرات امتیاز مرتب شوند و در صورت برابری، به ترتیب صعودی آیدی.
نمونه خروجی کوئری سوم
| username | total_contests | avg_rank | best_rank | total_rating_change | performance_rank | rating_improvement |
|---|---|---|---|---|---|---|
| tourist | (متغیر) | (متغیر) | 1 | (متغیر) | 1 | 2650 |
- برای هر سوال که حداقل ۵ ارسال (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نمایش دهد.
نتایج باید به ترتیب صعودی نرخ موفقیت و سپس به ترتیب صعودی آیدی سوال مرتب شوند.
نمونه خروجی کوئری چهارم
| 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 | (متغیر) | (متغیر) | (متغیر) | (متغیر) | (متغیر) | (متغیر) |
فایل نهایی
پس از پیادهسازی کوئریها، آن را در فایل queries.sql وارد کرده و سپس این فایل را آپلود کنید. کد شما باید به صورت زیر باشد:
-- Section1
Your first query here
-- Section2
Your second query here
-- Section3
Your third query here
-- Section4
Your fourth query here