Q01: Find High Earners (easy) Topic: Filtering & Value Thresholds People operations and total rewards teams segment compensation data to audit salary bands and market alignment. Queries must enforce strict numeric boundary conditions and guarantee deterministic sorting for audit reproducibility. Problem Statement Find all employees with a salary strictly greater than 70,000. Return the employee name and salary. Sort the results in descending order by salary, breaking ties alphabetically by employee name. Input Schemas & Sample Fixtures Table: employees idnamedepartmentsalaryhire_datemanager_id 1Alice SmithEngineering95000.002020-05-15NULL2Bob JonesEngineering80000.002021-08-0113Charlie BrownSales60000.002019-11-20NULL4Diana PrinceSales75000.002022-02-1035Evan WrightMarketing68000.002023-01-15NULL Expected Output namesalary Alice Smith95000.00Bob Jones80000.00Diana Prince75000.00 Solution Walkthrough Project Name and Salary: Select the name and salary attributes from the employees relation, omitting unneeded operational fields.Apply Strict Numeric Threshold: Filter records with WHERE salary > 70000. Using the strict inequality operator ensures that salaries exactly matching 70,000 are excluded.Sort Deterministically: Order results using ORDER BY salary DESC, name ASC so the highest compensation figures appear first, resolving salary ties in alphabetical order. Verified SQL Solution SELECT name, salary FROM employees WHERE salary > 70000 ORDER BY salary DESC, name ASC; Practice Find High Earners in In-Browser Sandbox →
Q02: Average Salary by Department (medium) Topic: Group Aggregations & Rounding Financial planning and department headcounts require baseline compensation benchmarking across business units. Queries aggregate personnel records by organizational unit, handle missing values, and format figures to standard currency precision. Problem Statement Calculate the average salary for each department, rounded to 2 decimal places. Return department and avg_salary. Exclude records where salary is unpopulated. Sort the results by avg_salary descending, then department descending. Input Schemas & Sample Fixtures Table: employees idnamedepartmentsalary 1Alice SmithEngineering95000.002Bob JonesEngineering80000.003Charlie BrownSales60000.004Diana PrinceSales75000.005Evan WrightMarketing68000.00 Expected Output departmentavg_salary Engineering87500.00Marketing68000.00Sales67500.00 Solution Walkthrough Partition Rows by Department: Group employee records by department so that each business unit yields a single summary row.Calculate Mean Compensation: Compute the arithmetic mean salary within each division using AVG(salary), which automatically disregards unpopulated entries.Format Decimal Precision: Round the calculated mean using ROUND(AVG(salary), 2) and assign the column alias avg_salary.Order by Compensation: Order the output using ORDER BY avg_salary DESC, department DESC to rank highest-paying departments first. Verified SQL Solution SELECT department, ROUND(AVG(salary), 2) AS avg_salary FROM employees WHERE salary IS NOT NULL GROUP BY department ORDER BY avg_salary DESC, department DESC; Practice Average Salary by Department in In-Browser Sandbox →
Q03: Post-Update Rating Drops (medium) Topic: Conditional Aggregations & Time Windows Chrome Web Store engineering teams track user review sentiment immediately following software version releases. Sharp score declines reveal breaking regressions, performance bottlenecks, or regressions in user experience. Problem Statement For each extension, compare reviews posted in the 14-day window before update_date (day -14 to day -1) with reviews posted in the 14-day window starting on update_date (day 0 to day 13). Only include extensions with at least 2 reviews in each window and where avg_rating_after is at least 0.50 lower than avg_rating_before. Round both averages to 2 decimal places. Return extension_name, avg_rating_before, and avg_rating_after. Sort by the rating drop descending, then extension_name ascending. Input Schemas & Sample Fixtures Table: extension_catalog extension_idextension_namedeveloperupdate_date 1TabMaster ProNimbus Software Ltd2024-03-152Focus TimerBrightPath Apps2024-03-10 Table: extension_reviews review_idextension_idratingreview_date 1152024-03-032142024-03-083122024-03-164112024-03-205252024-03-056232024-03-127222024-03-15 Expected Output extension_nameavg_rating_beforeavg_rating_after TabMaster Pro4.501.50 Solution Walkthrough Define Asymmetric Observation Windows: Join extension_catalog with extension_reviews on extension_id. Define the 14-day pre-update period as review_date >= update_date - INTERVAL '14 days' AND review_date < update_date, and the 14-day post-update period as review_date >= update_date AND review_date <= update_date + INTERVAL '13 days'.Compute Windowed Averages and Volumes: Nest conditional CASE expressions inside AVG() and COUNT() to compute mean ratings and feedback volume across both observation windows within a single aggregation step.Filter by Review Volume and Score Drop: Apply outer filtering requiring before_count >= 2, after_count >= 2, and a score drop of at least 0.50 points: (avg_rating_before - avg_rating_after) >= 0.5.Order by Severity of Drop: Sort the output by score decline descending: (avg_rating_before - avg_rating_after) DESC, breaking ties by extension_name ascending. Verified SQL Solution WITH review_metrics AS ( SELECT ec.extension_id, ec.extension_name, ROUND(AVG(CASE WHEN er.review_date >= ec.update_date - INTERVAL '14 days' AND er.review_date < ec.update_date THEN er.rating END), 2) AS avg_rating_before, ROUND(AVG(CASE WHEN er.review_date >= ec.update_date AND er.review_date <= ec.update_date + INTERVAL '13 days' THEN er.rating END), 2) AS avg_rating_after, COUNT(CASE WHEN er.review_date >= ec.update_date - INTERVAL '14 days' AND er.review_date < ec.update_date THEN 1 END) AS before_count, COUNT(CASE WHEN er.review_date >= ec.update_date AND er.review_date <= ec.update_date + INTERVAL '13 days' THEN 1 END) AS after_count FROM extension_catalog ec JOIN extension_reviews er ON er.extension_id = ec.extension_id GROUP BY ec.extension_id, ec.extension_name ) SELECT extension_name, avg_rating_before, avg_rating_after FROM review_metrics WHERE before_count >= 2 AND after_count >= 2 AND (avg_rating_before - avg_rating_after) >= 0.5 ORDER BY (avg_rating_before - avg_rating_after) DESC, extension_name ASC; Practice Post-Update Rating Drops in In-Browser Sandbox →
Q04: Department Compensation Review (hard) Topic: HAVING Filters & Headcount Aggregations Enterprise workforce planning audits departmental payroll allocations to identify divisions exceeding target compensation benchmarks. Such evaluations require computing team averages while retaining accurate employee counts across all records. Problem Statement Identify departments whose average employee salary is strictly above 75,000. Return the department name, average salary rounded to 2 decimal places as avg_salary, and the total number of employees as emp_count. Sort by avg_salary descending, then department descending. Input Schemas & Sample Fixtures Table: employees employee_idemployee_namedepartmentsalary 1Priya RamanSearch920002Marcus ChenSearch880003Diego OrtizAds710004Lena KowalskiAds660005Farah HaddadCloud69500 Expected Output departmentavg_salaryemp_count Search90000.002 Solution Walkthrough Partition Records by Department: Group the employee dataset by department to aggregate salary metrics at the departmental level.Calculate Average Salary and Total Headcount: Compute ROUND(AVG(salary), 2) AS avg_salary to determine mean compensation while disregarding NULL entries. Compute COUNT(*) AS emp_count to record total assigned personnel.Filter Departmental Averages with HAVING: Apply HAVING AVG(salary) > 75000 to filter out lower-earning departments and retain only divisions whose mean salary exceeds 75,000.Sort Qualifying Departments: Order the qualifying divisions using ORDER BY avg_salary DESC, department DESC. Verified SQL Solution SELECT department, ROUND(AVG(salary), 2) AS avg_salary, COUNT(*) AS emp_count FROM employees GROUP BY department HAVING AVG(salary) > 75000 ORDER BY avg_salary DESC, department DESC; Practice Department Compensation Review in In-Browser Sandbox →
Q05: Median Search Frequency (hard) Topic: Statistical Medians & PERCENTILE_CONT Google Search analytics evaluates typical user search frequency through non-parametric median distributions. Unlike arithmetic means, continuous medians remain robust against extreme outlier queries and viral traffic spikes. Problem Statement Given daily search frequency logs per term, find the continuous median frequency for each search query. Ignore missing frequencies before determining percentiles. Return search_term and median_freq. Sort by search_term ascending. Input Schemas & Sample Fixtures Table: search_frequency search_termfrequency data science300data science450data science600machine learning200machine learning350machine learning500 Expected Output search_termmedian_freq data science450machine learning350 Solution Walkthrough Exclude Missing Observations: Filter data with WHERE frequency IS NOT NULL to prevent missing records from skewing statistical distribution calculations.Group by Query Term: Group rows by search_term so each distinct search term forms an independent statistical cohort.Compute Continuous Median with PERCENTILE_CONT: Calculate the 50th percentile using PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY frequency). The WITHIN GROUP clause orders sample frequencies, while PERCENTILE_CONT interpolates continuous medians between adjacent values if necessary.Order Alphabetically: Order the output alphabetically using ORDER BY search_term ASC. Verified SQL Solution SELECT search_term, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY frequency) AS median_freq FROM search_frequency WHERE frequency IS NOT NULL GROUP BY search_term ORDER BY search_term ASC; Practice Median Search Frequency in In-Browser Sandbox →
Q06: Seven-Day Activation Retention (hard) Topic: Cohort Retention & Event Tracking Google Play product teams gauge new user onboarding health through early-stage activation metrics. Cohort analyses track how many distinct calendar days users open an app during their initial seven-day lifecycle and whether they remain active on the critical seventh day. Problem Statement For every user whose earliest event occurred in January 2025 (January 1 through January 31, inclusive), return user_id, signup_date (date of first event), active_days_first_7 (number of distinct calendar days active during the first 7 days including signup), and returned_on_day_7 (1 if active on signup_date + 6 days, else 0). Exclude events prior to signup. Sort results by user_id descending. Input Schemas & Sample Fixtures Table: app_events event_iduser_idevent_nameoccurred_at 11open2025-01-02 08:0021open2025-01-02 09:0031buy2025-01-03 10:0041open2025-01-09 10:0052open2025-01-10 11:0062open2025-01-16 11:0073open2025-01-31 12:0083open2025-02-01 12:00 Expected Output user_idsignup_dateactive_days_first_7returned_on_day_7 32025-01-312022025-01-102112025-01-0220 Solution Walkthrough Identify the January Cohort Baseline: Determine each user's initial event timestamp with MIN(occurred_at::date) AS signup_date. Restrict the cohort to users whose very first interaction occurred in January 2025 via the HAVING clause.Join the 7-Day Observation Window: Join cohort users back to app_events where occurred_at::date falls within the seven-day window: between signup_date and signup_date + INTERVAL '6 days'.Measure Active Days and Day 7 Retention: Calculate distinct active days using COUNT(DISTINCT occurred_at::date). Evaluate Day 7 retention using MAX(CASE WHEN occurred_at::date = signup_date + INTERVAL '6 days' THEN 1 ELSE 0 END) to return 1 if active on day seven, otherwise 0.Order by User Identifier: Sort the resulting cohort dataset using ORDER BY user_id DESC. Verified SQL Solution WITH user_signup AS ( SELECT user_id, MIN(occurred_at::date) AS signup_date FROM app_events GROUP BY user_id HAVING MIN(occurred_at::date) >= '2025-01-01' AND MIN(occurred_at::date) < '2025-02-01' ), cohort_activity AS ( SELECT s.user_id, s.signup_date, COUNT(DISTINCT e.occurred_at::date) AS active_days_first_7, MAX(CASE WHEN e.occurred_at::date = s.signup_date + INTERVAL '6 days' THEN 1 ELSE 0 END) AS returned_on_day_7 FROM user_signup s JOIN app_events e ON e.user_id = s.user_id AND e.occurred_at::date >= s.signup_date AND e.occurred_at::date <= s.signup_date + INTERVAL '6 days' GROUP BY s.user_id, s.signup_date ) SELECT user_id, signup_date, active_days_first_7, returned_on_day_7 FROM cohort_activity ORDER BY user_id DESC; Practice Seven-Day Activation Retention in In-Browser Sandbox →