SQLQuokka
Start practicing
QuestionsLearnSandboxFeaturesRoadmapFAQ
SQL Study Plan

SQL Subqueries and CTEs Practice Questions

Harder questions need more than one step. Learn to break a problem into stages with WITH clauses, compare rows against a group result with correlated subqueries and reuse derived tables.

20 questions · 4 Easy · 12 Medium · 4 Hard · about 3.7 hours · MySQL & PostgreSQL

What you'll practise

  • WITH clauses (CTEs)
  • Scalar subqueries
  • Correlated subqueries
  • Derived tables
  • Recursive CTEs

Learn the concepts first

Questions in this plan

  1. Customers Who Never Order

    Identify all customer records that have no matching entries in the Orders table.

    Easy✓ Solved

  2. Second Highest Delivery Fee

    Return the second-highest distinct delivery fee, or NULL if there isn't one.

    Easy✓ Solved

  3. Monthly Listing Ratings

    Average star rating for each listing in each month.

    Easy✓ Solved

  4. Largest Unique Number

    Find the largest number that appears exactly once.

    Easy✓ Solved

  5. YouTube: Breakout Videos Dominating Channels

    Find videos that account for at least 50% of their channel total views, calculating each video view percentage.

    Medium✓ Solved

  6. Second Highest Salary

    Find the second highest distinct salary from the Employee table without duplicate values.

    Medium✓ Solved

  7. Find Duplicate Rows to Remove (Keep Lowest ID)

    Formulate a query to eliminate duplicate email entries while preserving the record with smallest ID.

    Medium✓ Solved

  8. Nth Highest Salary

    Write a dynamic MySQL stored procedure or subquery to retrieve the Nth distinct salary ranking.

    Medium✓ Solved

  9. Amazon: Repeat Purchase Rate

    Calculate the percentage of all registered customers who have placed two or more orders, rounded to 2 decimal places.

    Medium✓ Solved

  10. Amazon: Above City Average Spend

    Find customers whose total cumulative purchase spend exceeds the average customer spend in their respective city.

    Medium✓ Solved

  11. Amazon: First Order Details

    Retrieve each customer's very first order, resolving date ties by selecting the lowest order_id.

    Medium✓ Solved

  12. Users Who Heard an Entire Playlist

    Identify listeners who played every track of a given playlist at least once.

    Medium✓ Solved

  13. Most Watched Title per Genre

    Find the title with the highest total watch time in each genre, keeping ties.

    Medium✓ Solved

  14. Org Chart Downstream Reports

    List everyone who reports, directly or indirectly, to a given manager along with their depth.

    Medium✓ Solved

  15. Ordered Purchase Funnel

    Measure a three-step conversion funnel where the steps must happen in order.

    Medium✓ Solved

  16. Camera Buyers Without Memory Cards

    Find customers who bought a camera but never bought a memory card, and count their cameras.

    Medium✓ Solved

  17. Products Never Purchased Together

    Cross-reference product catalog against order item baskets to detect inventory pair gaps.

    Hard✓ Solved

  18. Amazon: Prime Monthly Churn Rate

    Calculate monthly Prime subscriber churn rates using calendar intervals and conditional status aggregations.

    Hard✓ Solved

  19. Amazon: Monthly Cohort Retention

    Analyze customer retention cohorts across 1-month and 2-month subsequent repurchase cycles.

    Hard✓ Solved

  20. Longest Login Streaks

    Find users who logged in on at least three consecutive days and report their best streak.

    Hard✓ Solved