Mini project: coding contest leaderboard

60 min · Advanced

A contest leaderboard is a ranking over totals: score each person's best attempts, rank them inside each contest, keep a running total across the season, and publish the result as a view the website can read.

What you'll build

Your college coding club runs a weekly online contest with three problems: A (100 points), B (200) and C (300). Contestants may submit a problem several times, and only their best score per problem counts. The tables and three weeks of submissions are given in data/shop.sql; your queries.sql builds the views and answers the questions the club asks every Sunday night.

Your file prints the standings of each contest (ties broken by who finished earlier), the season standings after weeks 2 and 3 with how many places each person moved, and the overall leaderboard. The leaderboard is saved as a view, so the club's website only needs SELECT * FROM leaderboard.

Sample run

+-----------------+-------+--------------+-------+----------+
| title           | place | handle       | score | finished |
+-----------------+-------+--------------+-------+----------+
| Weekly Sprint 1 |     1 | chitra_dev   |   450 | 11:20:00 |
| Weekly Sprint 1 |     2 | ananya_codes |   300 | 10:40:00 |
| Weekly Sprint 1 |     3 | esha_algo    |   300 | 10:45:00 |
| Weekly Sprint 1 |     4 | byte_bharath |   300 | 10:55:00 |
| Weekly Sprint 1 |     5 | dev_loops    |   100 | 10:25:00 |
| Weekly Sprint 2 |     1 | ananya_codes |   600 | 11:02:00 |
| Weekly Sprint 2 |     2 | byte_bharath |   400 | 11:15:00 |
| Weekly Sprint 2 |     3 | dev_loops    |   300 | 10:48:00 |
| Weekly Sprint 2 |     4 | chitra_dev   |   100 | 10:22:00 |
| Weekly Sprint 3 |     1 | dev_loops    |   600 | 11:05:00 |
| Weekly Sprint 3 |     2 | byte_bharath |   550 | 11:10:00 |
| Weekly Sprint 3 |     3 | esha_algo    |   300 | 10:41:00 |
| Weekly Sprint 3 |     4 | chitra_dev   |   300 | 10:52:00 |
| Weekly Sprint 3 |     5 | ananya_codes |   100 | 10:11:00 |
+-----------------+-------+--------------+-------+----------+
+------------+--------------+-------+-------+-------+-------+
| contest_id | handle       | score | total | place | moved |
+------------+--------------+-------+-------+-------+-------+
|          2 | ananya_codes |   600 |   900 |     1 |     1 |
|          2 | byte_bharath |   400 |   700 |     2 |     0 |
|          2 | chitra_dev   |   100 |   550 |     3 |    -2 |
|          2 | dev_loops    |   300 |   400 |     4 |     1 |
|          2 | esha_algo    |     0 |   300 |     5 |    -3 |
|          3 | byte_bharath |   550 |  1250 |     1 |     1 |
|          3 | ananya_codes |   100 |  1000 |     2 |    -1 |
|          3 | dev_loops    |   600 |  1000 |     2 |     2 |
|          3 | chitra_dev   |   300 |   850 |     4 |    -1 |
|          3 | esha_algo    |   300 |   600 |     5 |     0 |
+------------+--------------+-------+-------+-------+-------+
+-------+--------------+----------------+----------+-------------+--------------+
| place | handle       | college        | contests | total_score | best_contest |
+-------+--------------+----------------+----------+-------------+--------------+
|     1 | byte_bharath | VIT Vellore    |        3 |        1250 |          550 |
|     2 | ananya_codes | NIT Trichy     |        3 |        1000 |          600 |
|     2 | dev_loops    | IIIT Hyderabad |        3 |        1000 |          600 |
|     4 | chitra_dev   | PES Bengaluru  |        3 |         850 |          450 |
|     5 | esha_algo    | COEP Pune      |        2 |         600 |          300 |
+-------+--------------+----------------+----------+-------------+--------------+

Requirements

Your program should do all of these.

Work on the three tables in data/shop.sql: contests (contest_id, title, held_on), contestants (contestant_id, handle, college) and submissions (submission_id, contest_id, contestant_id, problem, score, submitted_at). Do not create or change tables in queries.sql.
A view contest_scores gives one row per contestant per contest: the sum of the best score for each problem, and the time of the last submission.
Contest standings use RANK() per contest: higher score first, and an earlier finish wins a tie on score. Columns, in order: title, place, handle, score, finished (the time of day of the last submission). Rows are sorted by contest, then place, then handle.
Season standings include every contestant after every contest; a missed contest counts as 0 points.
Each season row shows the running total (SUM ... OVER) and how many places the person moved since the previous week (LAG). Show contests 2 and later only, with columns contest_id, handle, score, total, place, moved, sorted by contest, place, then handle.
A view leaderboard ranks everyone by total score with RANK() and shows the college, contests played and best single contest. The last report prints place, handle, college, contests, total_score, best_contest, sorted by place, then handle.
queries.sql runs from start to finish on a database that already holds the tables, and can be run again (it drops its own views first).

Build it step by step in the Build tab (5 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

What's next

You finished the preview of Subqueries, Views & Window Functions

Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.

Create account I already have an account

Next step

Start building

5 milestones

What you'll build

Sample run

+-----------------+-------+--------------+-------+----------+
| title           | place | handle       | score | finished |
+-----------------+-------+--------------+-------+----------+
| Weekly Sprint 1 |     1 | chitra_dev   |   450 | 11:20:00 |
| Weekly Sprint 1 |     2 | ananya_codes |   300 | 10:40:00 |
| Weekly Sprint 1 |     3 | esha_algo    |   300 | 10:45:00 |
| Weekly Sprint 1 |     4 | byte_bharath |   300 | 10:55:00 |
| Weekly Sprint 1 |     5 | dev_loops    |   100 | 10:25:00 |
| Weekly Sprint 2 |     1 | ananya_codes |   600 | 11:02:00 |
| Weekly Sprint 2 |     2 | byte_bharath |   400 | 11:15:00 |
| Weekly Sprint 2 |     3 | dev_loops    |   300 | 10:48:00 |
| Weekly Sprint 2 |     4 | chitra_dev   |   100 | 10:22:00 |
| Weekly Sprint 3 |     1 | dev_loops    |   600 | 11:05:00 |
| Weekly Sprint 3 |     2 | byte_bharath |   550 | 11:10:00 |
| Weekly Sprint 3 |     3 | esha_algo    |   300 | 10:41:00 |
| Weekly Sprint 3 |     4 | chitra_dev   |   300 | 10:52:00 |
| Weekly Sprint 3 |     5 | ananya_codes |   100 | 10:11:00 |
+-----------------+-------+--------------+-------+----------+
+------------+--------------+-------+-------+-------+-------+
| contest_id | handle       | score | total | place | moved |
+------------+--------------+-------+-------+-------+-------+
|          2 | ananya_codes |   600 |   900 |     1 |     1 |
|          2 | byte_bharath |   400 |   700 |     2 |     0 |
|          2 | chitra_dev   |   100 |   550 |     3 |    -2 |
|          2 | dev_loops    |   300 |   400 |     4 |     1 |
|          2 | esha_algo    |     0 |   300 |     5 |    -3 |
|          3 | byte_bharath |   550 |  1250 |     1 |     1 |
|          3 | ananya_codes |   100 |  1000 |     2 |    -1 |
|          3 | dev_loops    |   600 |  1000 |     2 |     2 |
|          3 | chitra_dev   |   300 |   850 |     4 |    -1 |
|          3 | esha_algo    |   300 |   600 |     5 |     0 |
+------------+--------------+-------+-------+-------+-------+
+-------+--------------+----------------+----------+-------------+--------------+
| place | handle       | college        | contests | total_score | best_contest |
+-------+--------------+----------------+----------+-------------+--------------+
|     1 | byte_bharath | VIT Vellore    |        3 |        1250 |          550 |
|     2 | ananya_codes | NIT Trichy     |        3 |        1000 |          600 |
|     2 | dev_loops    | IIIT Hyderabad |        3 |        1000 |          600 |
|     4 | chitra_dev   | PES Bengaluru  |        3 |         850 |          450 |
|     5 | esha_algo    | COEP Pune      |        2 |         600 |          300 |
+-------+--------------+----------------+----------+-------------+--------------+

Requirements

Your program should do all of these.

Work on the three tables in data/shop.sql: contests (contest_id, title, held_on), contestants (contestant_id, handle, college) and submissions (submission_id, contest_id, contestant_id, problem, score, submitted_at). Do not create or change tables in queries.sql.

A view contest_scores gives one row per contestant per contest: the sum of the best score for each problem, and the time of the last submission.

Contest standings use RANK() per contest: higher score first, and an earlier finish wins a tie on score. Columns, in order: title, place, handle, score, finished (the time of day of the last submission). Rows are sorted by contest, then place, then handle.

Season standings include every contestant after every contest; a missed contest counts as 0 points.

Each season row shows the running total (SUM ... OVER) and how many places the person moved since the previous week (LAG). Show contests 2 and later only, with columns contest_id, handle, score, total, place, moved, sorted by contest, place, then handle.

A view leaderboard ranks everyone by total score with RANK() and shows the college, contests played and best single contest. The last report prints place, handle, college, contests, total_score, best_contest, sorted by place, then handle.

queries.sql runs from start to finish on a database that already holds the tables, and can be run again (it drops its own views first).