KillSQL
Sign in

Tracks/Subqueries and CTEs

Subqueries and CTEs

Nested queries, EXISTS, and WITH clauses — including recursive CTEs.

Start this track
subquery
correlated subquery
CTE
recursive CTE
EXISTS
IN

0/122 solved

  1. 1—020. Customers from Specific Cities
    easy
  2. 2—121. Filter Products by Category Using IN
    easy
  3. 3—160. Find Employees Earning Above Average Salary
    easy
  4. 4—323. Filter Employees by Status Using IN
    easy
  5. 5—329. Customers Who Have Placed at Least One Order
    easy
  6. 6—035. Customers with At Least One Order
    medium
  7. 7—036. Customers with No Orders
    medium
  8. 8—038. Employees Earning Above Their Department Average
    medium
  9. 9—041. Top-Earning Departments (CTE)
    medium
  10. 10—043. First Order per Customer
    medium
  11. 11—046. Month-over-Month Revenue Change
    medium
  12. 12—047. Preview Next Month Revenue with LEAD
    medium
  13. 13—051. Top 3 Products per Category by Price
    medium
  14. 14—054. Year-over-Year Order Revenue Growth
    medium
  15. 15—056. Most Recent Order per Customer
    medium
  16. 16—059. Second Highest Salary
    medium
  17. 17—061. Cumulative Revenue Percentage by Department
    medium
  18. 18—062. Products with Above-Average Rating
    medium
  19. 19—065. Category Revenue Summary (Chained CTEs)
    medium
  20. 20—066. Top-Spending Customers (Derived Table)
    medium
  21. 21—071. Rank Departments by Total Payroll
    medium
  22. 22—075. Average Days Between Customer Orders
    medium
  23. 23—077. Deduplicate Rows with ROW_NUMBER
    medium
  24. 24—078. Customers with High-Value Orders
    medium
  25. 25—079. Customer Cohort by First Order Month
    medium
  26. 26—080. 3-Month Moving Average Revenue
    medium
  27. 27—082. Retained Customers: Ordered in Both Month 1 and Month 2
    medium
  28. 28—083. Find Gaps in Sequential Order IDs
    medium
  29. 29—087. Third Highest Salary
    medium
  30. 30—089. Category with the Highest Average Price
    medium
  31. 31—174. Employees Earning Above Their Department Average
    medium
  32. 32—175. Customers Who Have Placed at Least One Order
    medium
  33. 33—176. Products That Have Never Been Ordered
    medium
  34. 34—177. Chained CTEs for Layered Sales Analysis
    medium
  35. 35—178. Traverse Org Chart Hierarchy with Recursive CTE
    medium
  36. 36—193. Deduplicate Orders Keeping the Latest Entry
    medium
  37. 37—194. Top 2 Products Per Category by Revenue
    medium
  38. 38—196. Count New Customers Acquired Each Month
    medium
  39. 39—204. Most Recent Order Per Customer Using LATERAL JOIN
    medium
  40. 40—208. User Cohort Retention by First Purchase Month
    medium
  41. 41—209. Month-2 Retention Rate Calculation
    medium
  42. 42—210. Funnel Analysis with Step-by-Step Conversion Rates
    medium
  43. 43—211. ABC Inventory Classification by Cumulative Revenue
    medium
  44. 44—212. Customer Tier Segmentation Using NTILE
    medium
  45. 45—215. Find the Most Frequently Occurring Rating (Mode)
    medium
  46. 46—221. Multi-Level Aggregation (Aggregate of Aggregates)
    medium
  47. 47—222. Scalar Subquery in SELECT Clause
    medium
  48. 48—223. Subquery in FROM Clause (Derived Table / Inline View)
    medium
  49. 49—235. Employees in Top 10% by Salary Using PERCENT_RANK
    medium
  50. 50—243. Customers Who Purchased from Every Category
    medium
  51. 51—246. Longest Gap Between Consecutive Orders per Customer
    medium
  52. 52—248. New vs Returning Customers per Month
    medium
  53. 53—249. Repeat Purchase Rate Calculation
    medium
  54. 54—250. Customer Purchase Frequency Distribution
    medium
  55. 55—251. RFM Analysis — Recency, Frequency, Monetary Buckets
    medium
  56. 56—261. Remove Duplicates Keeping the Most Recent Record
    medium
  57. 57—263. Detecting Anomalies Using Z-Score Approach
    medium
  58. 58—274. Finding Maximum Streak of Consecutive Login Days
    medium
  59. 59—275. Customer Segments by Spending Quartile
    medium
  60. 60—276. Product Categories with Month-over-Month Revenue Decline
    medium
  61. 61—278. Transactions More Than 3 Standard Deviations Above Mean
    medium
  62. 62—280. Multi-Step CTE Pipeline for Complex Analytics
    medium
  63. 63—331. Customers With Strictly Increasing Order Values
    medium
  64. 64—332. Rank Products by Revenue Within Each Category
    medium
  65. 65—334. Churn Detection: Customers Inactive for 90+ Days
    medium
  66. 66—335. Top-Selling Product in Each Region
    medium
  67. 67—339. Detect Same-Direction Price and Quantity Changes
    medium
  68. 68—342. Customer Order Gap Alert (30–60 Days)
    medium
  69. 69—343. Products Ordered by Every Customer
    medium
  70. 70—344. Top Salesperson Each Quarter
    medium
  71. 71—349. Revenue From First-Time vs Repeat Purchases
    medium
  72. 72—351. Most Popular Product Each Month
    medium
  73. 73—352. Customers Who Upgraded Their Loyalty Tier
    medium
  74. 74—354. Category Market Share of Total Revenue
    medium
  75. 75—360. Median Days Between Customer Purchases
    medium
  76. 76—364. High-Value Order Alert (2× Customer Average)
    medium
  77. 77—366. Time Between Order Status Transitions
    medium
  78. 78—367. Assign Customer Lifetime Value Tiers
    medium
  79. 79—370. Peak Order Hour per Day
    medium
  80. 80—371. Regional Sales Rep Performance with Rank
    medium
  81. 81—373. Win-Back: Customers Who Returned After 90-Day Dormancy
    medium
  82. 82—374. Invoice Aging Bucket Report
    medium
  83. 83—376. Percentage of Orders With Multiple Products
    medium
  84. 84—378. Product Revenue Contribution via CTE
    medium
  85. 85—379. Weekly Signup Cohort Active Users
    medium
  86. 86—091. Managers Paid Above Team Average
    hard
  87. 87—093. Median Salary Without PERCENTILE
    hard
  88. 88—094. Org Chart: All Direct and Indirect Reports
    hard
  89. 89—095. Session Identification from Login Events
    hard
  90. 90—096. Churned vs Retained Customers
    hard
  91. 91—097. Cohort Retention Matrix (Months 0–3)
    hard
  92. 92—099. Running Balance with Overdraft Detection
    hard
  93. 93—100. Employee Hierarchy Depth
    hard
  94. 94—281. Recursive Bill of Materials Explosion
    hard
  95. 95—282. Full Org Chart Path as String
    hard
  96. 96—283. Graph Shortest Path via BFS
    hard
  97. 97—284. Fibonacci Sequence via Recursive CTE
    hard
  98. 98—286. Session Stitching with 30-Minute Gap Tolerance
    hard
  99. 99—287. Merge Overlapping Subscription Intervals
    hard
  100. 100—288. Time-Series Gap Filling with Zero Revenue Days
    hard
  101. 101—293. Fraud Detection: 3+ Transactions Within 1 Hour
    hard
  102. 102—294. Customer Lifetime Value Projection
    hard
  103. 103—295. Cohort Retention Analysis Pipeline
    hard
  104. 104—296. Nearest Price Match Across Two Catalogs
    hard
  105. 105—297. Balanced Task Distribution with NTILE
    hard
  106. 106—298. String Parsing: Extract Structured Data from Concatenated Field
    hard
  107. 107—299. Weighted Composite Product Ranking
    hard
  108. 108—300. Maximum Concurrent Bookings per Room
    hard
  109. 109—381. Maximum Non-Overlapping Meetings
    hard
  110. 110—382. Three-Day Moving Median Revenue
    hard
  111. 111—383. Required Skills Missing per Employee
    hard
  112. 112—385. Two-Hop Follower Influence Score
    hard
  113. 113—386. Three-Period Loan Amortization
    hard
  114. 114—387. Four-Team Single-Elimination Bracket
    hard
  115. 115—388. Org-Chart Budget Rollup
    hard
  116. 116—390. Weekly Cohort Retention Percentages
    hard
  117. 117—391. Project Critical Path Length
    hard
  118. 118—393. Checkout Funnel Drop-Off Rates
    hard
  119. 119—394. Collaborative Filtering Recommendations for User 1
    hard
  120. 120—396. Exponential Moving Average with Alpha 0.3
    hard
  121. 121—398. Multi-Level Sales Commission
    hard
  122. 122—400. Monthly Revenue and New-Customer Dashboard
    hard