Optimize Slow SQL on Large Data | Dataford Interview Questions

Optimize a provided SQL query that is currently performing poorly on a large dataset for HackerRank.

Rewrite the query so it returns the same business result with less repeated work. Include only submissions from January 2025 whose user and challenge records exist.

Output

One row per challenge with submissions in the date range.
Columns: difficulty, challenge_id, title, total_submissions, accepted_submissions, distinct_submitters, acceptance_rate, and performance_rank.
Rank challenges within difficulty by acceptance rate descending, then total submissions descending, then challenge ID ascending. Order by difficulty, rank, and challenge ID.

Schema

users

Column	Type	Description
user_idPK	INT	Unique HackerRank user identifier
username	VARCHAR(100)	Public username
country	VARCHAR(80)	User country

challenges

Column	Type	Description
challenge_idPK	INT	Unique challenge identifier
title	VARCHAR(200)	Challenge title
difficulty	VARCHAR(30)	Challenge difficulty level

submissions

Column	Type	Description
submission_idPK	INT	Unique submission identifier
user_id	INT	Submitting user identifier
challenge_id	INT	Submitted challenge identifier
submitted_at	TIMESTAMP	Submission timestamp
status	VARCHAR(20)	Submission outcome
score	DECIMAL(5,2)	Submission score

Your question is Optimize Slow SQL on Large Data. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem