Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · HubSpot
Track individual and team sales performance against quotas to identify high performers, underachievers, and pacing trends.
It's the March review, and HubSpot's sales leadership needs an honest read on five reps against identical ₹5L monthly quotas: who exceeded, who's on track, and who needs a coaching plan — plus the uncomfortable question of whether the quotas themselves are fair.
Per rep the query computes deals closed, actual revenue (won deals in March only), quota attainment %, and amount vs quota, then adds a performance rank (a window function ordering reps by attainment) and a status: Exceeding-Quota (120%+), On-Track (100%+), Below-Target (75%+), or At-Risk. Ranks 1–2 earn Top-Performer, ranks 4–5 get Needs-Coaching.
Read the labels skeptically — that's the actual test. A rep can rank #2 Top-Performer while missing quota at 90% (rank flatters, the business feels the shortfall), and 36% attainment in Central may indict the territory or the quota, not the rep. Diagnose before coaching.
Sales reps with rep_id, territory, monthly_quota; deals with deal_id, rep_id, deal_amount, deal_close_date, deal_stage (Won/Lost/Open).
CREATE TABLE sales_reps (
rep_id INTEGER PRIMARY KEY,
rep_name TEXT,
territory TEXT,
monthly_quota DECIMAL(10,2)
);
CREATE TABLE deals (
deal_id INTEGER PRIMARY KEY,
rep_id INTEGER,
deal_amount DECIMAL(10,2),
deal_close_date DATE,
deal_stage TEXT,
FOREIGN KEY(rep_id) REFERENCES sales_reps(rep_id)
);Your target
Track individual and team sales performance against quotas to identify high performers, underachievers, and pacing trends.
Loading the expected output…
WITH monthly_sales AS (
SELECT
sr.rep_id,
sr.rep_name,
sr.territory,
sr.monthly_quota,
COUNT(DISTINCT d.deal_id) as deals_closed,
ROUND(SUM(CASE WHEN d.deal_stage = 'Won' THEN d.deal_amount ELSE 0 END), 2) as actual_revenue,
ROUND(SUM(CASE WHEN d.deal_stage = 'Won' THEN d.deal_amount ELSE 0 END) / sr.monthly_quota * 100, 1) as quota_achievement_pct
FROM sales_reps sr
LEFT JOIN deals d ON sr.rep_id = d.rep_id
AND STRFTIME('%Y-%m', d.deal_close_date) = '2024-03'
GROUP BY sr.rep_id, sr.rep_name, sr.territory, sr.monthly_quota
),
performance_ranking AS (
SELECT
rep_id,
rep_name,
territory,
monthly_quota,
deals_closed,
actual_revenue,
quota_achievement_pct,
ROUND(actual_revenue - monthly_quota, 2) as amount_vs_quota,
RANK() OVER (ORDER BY quota_achievement_pct DESC) as performance_rank,
CASE
WHEN quota_achievement_pct >= 120 THEN 'Exceeding-Quota'
WHEN quota_achievement_pct >= 100 THEN 'On-Track'
WHEN quota_achievement_pct >= 75 THEN 'Below-Target'
ELSE 'At-Risk'
END as performance_status
FROM monthly_sales
)
SELECT
rep_id,
rep_name,
territory,
monthly_quota,
deals_closed,
actual_revenue,
quota_achievement_pct,
amount_vs_quota,
performance_rank,
performance_status,
CASE
WHEN performance_rank <= 2 THEN 'Top-Performer'
WHEN performance_rank >= 4 THEN 'Needs-Coaching'
ELSE 'Consistent'
END as coaching_recommendation
FROM performance_ranking
ORDER BY performance_rank ASC;Step-by-step Solution:
Create monthly_sales CTE to aggregate rep performance. Join sales_reps with deals, filtering for March 2024 using STRFTIME. Count closed deals and SUM only 'Won' deals (not Open/Lost) for actual_revenue. Divide actual by quota to get quota_achievement_pct.
In performance_ranking CTE, use RANK() OVER to rank reps by achievement percentage (highest first). Calculate amount_vs_quota (actual minus quota) to show dollar gap. Classify status: >=120% exceeding, >=100% on-track, >=75% below-target, <75% at-risk.
Map coaching recommendations: top 2 performers (rank 1-2) marked for best-practice sharing, rank 4+ need coaching, others are consistent.
Order by performance_rank to put best performers first. This enables sales leadership to identify top talent, plan coaching sessions, and forecast quarter-end performance.
Computing the reference output…
Only 1 of 5 reps hit quota in March: Priya Sharma (South, 100%) takes rank 1 with Neha Patel (West, 90%) right behind — both are the Top-Performers to learn from. The Central and East territories sit at 36-37% attainment (Rs.3.15L+ short each) and need coaching plans now; the North at 84% is one enterprise deal from quota.
What the interviewer asks next
“Neha Patel missed quota at 90% yet ranks #2 Top-Performer — is she a top performer or a quota miss?”
Strong answer covers: Rank vs absolute: the label flatters by rank while the business feels the Rs.50K shortfall — report both, and question a system where #2 still misses plan.
“Central and East sit at 36–37% — coaching plans, or is coaching the wrong prescription?”
Strong answer covers: Diagnose before coaching: pipeline coverage, territory potential, and deal slippage distinguish a skill gap from a territory or quota-setting problem.
“Every territory carries the identical Rs.5L quota — fair?”
Strong answer covers: Quota-setting critique: equal quotas across unequal territories manufacture underperformance; argue for potential-based quotas using pipeline and historical yield.
“Rajesh closed the most deals (4) yet ranks 3rd at 84% — his average deal (Rs.105K) trails Priya's (Rs.167K). Activity problem or deal-size problem?”
Strong answer covers: Decomposing attainment into volume × size: Rajesh needs bigger deals, not more calls — the coaching plan for him (enterprise selling, qualification) is the opposite of Vikram's (pipeline generation).
“Neha's 2 deals average Rs.225K — classic whale hunting. What breaks if everyone copies her?”
Strong answer covers: Portfolio risk of whale strategies: two-deal quarters have massive variance — one slip and 90% becomes 45%, so the team needs a mix of whales and farmers, not five Nehas.
“Propose one alternative to attainment % that would change the rankings — and defend when yours is fairer.”
Strong answer covers: Metric design under fire: attainment-per-pipeline-rupee rewards efficiency, quota-per-territory-potential rewards fairness — each flips the leaderboard, and choosing is a values statement about what sales should optimize.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.