Challenges

Warm-up, core and boss MySQL challenges. Every submission is graded on Handler_read_* counters, not wall-clock time.

• What are the categories of bugs we have? · Warm-up · indexing, distinct

  Given a table bugs(id, token, title, category, device, reported_at, created_at, updated_at) with about 10 million rows, select all distinct bug categories. Output the column (category).

• Count em bugs · Boss · indexing, primary-key, binary-search

  Given a table bugs(id, token, title, category, device, reported_at, created_at, updated_at) with about 10 million rows, find how many bugs were created on 2019-03-01 or later. Output the column (coun…

• Find the title · Core · indexing, leftmost-prefix

  Given a table bugs(id, token, title, category, device, reported_at, created_at, updated_at) with about 10 million rows, find the title of the bug with token = 'token660' and reported_at = '2020-08-30…

• The first deleted bug · Core · primary-key, anti-join, limit

  Given a table bugs(id, token, title, category, device, reported_at, created_at, updated_at) with about 10 million rows, some bugs were deleted and left holes in the id sequence. Find the smallest id…

• Late or lost · Core · indexing, sorting, union, limit

  Given 2 tables, customers(id, name, country) and rentals(id, store_id, customer_id, inventory_id, status, rental_date, amount) with 3 million rows, list the 10 most recent rentals of store 12 whose s…

• Late at store 7 · Warm-up · indexing, sorting, limit

  Given 2 tables, customers(id, name, country) and rentals(id, store_id, customer_id, inventory_id, status, rental_date, amount) with 3 million rows, list the 10 most recent rentals of store 7 whose st…

• Late returns dashboard · Boss · indexing, sorting, leftmost-prefix, lateral, limit

  Given 2 tables, customers(id, name, country) and rentals(id, store_id, customer_id, inventory_id, status, rental_date, amount) with 3 million rows across 50 stores, list the 15 most recent rentals wh…

• Everyone's latest report · Boss · indexing, top-n-per-group, loose-index-scan, sorting

  Given a table bugs(id, token, title, category, device, reported_at, created_at, updated_at) with about 10 million rows, find for every reporter (token) the bug they reported most recently: the one wi…

• Load more · Core · indexing, pagination, sorting

  Given a table bugs(id, token, title, category, device, reported_at, created_at, updated_at) with about 10 million rows, an app lists the bugs of the reporter token = 'token4242' sorted by reported_at…

• Find the most popular course · Warm-up · aggregation, joins, filtering

  Given 3 tables, students(id, name), courses(id, name), grades(id, course_id, student_id, grade), find the name of the most popular course: the one with the most enrolled students who are passing, i.e…

• Where did I rank? · Warm-up · window-functions, filtering, joins

  Given 3 tables, students(id, name), courses(id, name), grades(id, course_id, student_id, grade), find the rank of the student 'Ahmed Abbas' in the course 'Database Systems'. The rank is 1 plus the nu…

• The iPhone X bug closest to New Year · Boss · primary-key, binary-search, nearest-neighbor, limit

  Given a table bugs(id, token, title, category, device, reported_at, created_at, updated_at) with about 10 million rows, find the bug from device = 'iPhone X' whose created_at is closest to '2019-01-0…

• The five newest bugs · Warm-up · indexing, primary-key, limit

  Given a table bugs(id, token, title, category, device, reported_at, created_at, updated_at) with about 10 million rows, list the 5 most recently created bugs, newest first. Output the columns (id, ti…

• Latest rentals from Egypt · Core · indexing, sorting, joins, join-order, limit

  Given 2 tables, customers(id, name, country) and rentals(id, store_id, customer_id, inventory_id, status, rental_date, amount) with 3 million rows, list the 10 most recent rentals made by customers w…

• A reporter's 2020 · Core · indexing, sargable, range

  Given a table bugs(id, token, title, category, device, reported_at, created_at, updated_at) with about 10 million rows, count how many bugs the reporter with token = 'token777' reported during 2020.…

• Generate students grade report · Warm-up · joins, sorting

  Given 3 tables, students(id, name), courses(id, name), grades(id, course_id, student_id, grade), list every course each student is enrolled in along with the grade they scored in it. Order by student…

• Find the top 100 students · Warm-up · aggregation, joins, sorting

  Given 3 tables, students(id, name), courses(id, name), grades(id, course_id, student_id, grade), find the top 100 students based on their average grade, sorted descending by the average grade. If mul…