Work through 70 advanced SQL exercises in a structured sequence. Each challenge includes a clear problem, schema context and an interactive query editor.
Rank employees by salary within each department using RANK(), then return each employee's department, salary, and position. Return columns: first_name, last_name, department_id, salary, salary_rank. Order results by: salary DESC) AS salary_rank FROM employees ORDER BY department_id, salary_rank.
Calculate a chronological running total of order amounts and return every order with its date, amount, and cumulative revenue. Return columns: id, order_date, total_amount, running_total. Order results by: order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders ORDER BY order_date.
Using a CTE, find the highest-paid employee in each department. Return columns: department_id, first_name, last_name, max_salary. Order results by: e.department_id.
Summarize monthly order volume and revenue, then compare each month with the previous month using LAG(). Return columns: month, orders, revenue, prev_revenue, revenue_change. Order results by: month) AS prev_revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change FROM monthly ORDER BY month.
Divide employees into four salary quartiles with NTILE(4), then return each employee's name, salary, and quartile. Return columns: first_name, salary, quartile. Order results by: salary DESC) AS quartile FROM employees ORDER BY salary DESC.
Use DENSE_RANK() to rank products by price inside each category while keeping equal prices at the same rank. Return columns: name, category, price, price_rank. Order results by: price DESC) AS price_rank FROM products ORDER BY category, price_rank, name.
Number each customer's orders chronologically with a window function and return the customer, order date, and sequence number. Return columns: customer_id, order_id, order_date, order_number. Order results by: order_date, id) AS order_number FROM orders ORDER BY customer_id, order_number.
Use a CTE to return the three highest-paid employees. Return columns: first_name, last_name, salary, salary_rank. Order results by: salary DESC) AS salary_rank FROM employees) SELECT first_name, last_name, salary, salary_rank FROM ranked WHERE salary_rank <= 3 ORDER BY salary_rank.
Calculate each employee's percentage share of their department payroll. Return columns: first_name, department_id, salary, payroll_share. Order results by: department_id, salary DESC.
Compare every order amount with the previous chronological order using LAG(), while preserving the complete order sequence. Return columns: id, order_date, total_amount, previous_amount. Order results by: order_date, id) AS previous_amount FROM orders ORDER BY order_date, id.
Return customers who placed at least 1 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 2 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 3 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 4 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 5 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 6 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 7 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 8 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Find customers whose total completed-order spend exceeds 250. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 500. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 750. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 900. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1200. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1500. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1800. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 2500. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
List order items with quantity at least 1, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 2, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 3, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 4, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 5, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 6, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 7, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 8, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
Summarize order statuses whose total revenue is above 250. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 750. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 900. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1200. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1800. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 2500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Count each customer's orders placed on or after 2024-01-01. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-01-12. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-01-20. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-02-01. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-02-15. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-03-01. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-03-08. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-03-12. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
For each marketplace, return active sellers that offer at least one active listing in every category required by that marketplace. Ignore marketplaces with no requirements. Return marketplace_id and seller_id ordered by both columns. Return columns: marketplace_id, seller_id.
Find canonical product pairs appearing together in at least two distinct completed baskets. Repeated lines for a product in one basket count once. Return product_a, product_b and basket_count, ordered by basket_count descending then product IDs. Return columns: product_a, product_b, basket_count.
Return every pair of subscriptions for the same customer whose inclusive active periods overlap. A NULL end_date means the subscription is still active. Return customer_id, subscription_a, subscription_b, overlap_start and overlap_end; use NULL overlap_end for an open-ended overlap. Return columns: customer_id, subscription_a, subscription_b, overlap_start, overlap_end.
For every order, select the customer's address version valid at ordered_at. Validity is inclusive at valid_from and exclusive at valid_to; NULL valid_to is open-ended. If versions share valid_from, choose the highest address_id. Return order_id, address_id and city. Return columns: order_id, address_id, city.
Show the complete escalation ownership for open tickets: assigned agent, that agent's team lead, and the lead's support director. Exclude tickets whose chain is incomplete. Return ticket_id, agent_name, lead_name and director_name. Return columns: ticket_id, agent_name, lead_name, director_name.
Find canonical pairs of active sales representatives who currently share at least two distinct customers. Duplicate assignment history must not inflate the count. Return rep_a, rep_b and shared_customers. Return columns: rep_a, rep_b, shared_customers.
For each non-empty product category, return customers who bought every active product in that category through completed purchases. Multiple purchases of the same product count once. Return category and customer_id. Return columns: category, customer_id.
An activity is a login, purchase, or support event in customer_activity. A return event is re-engagement when it occurs at least 90 full days after that customer's immediately previous event; exactly 90 days qualifies. Return customer_id, return_event_id, return_at, previous_activity_at and inactivity_days. Return columns: customer_id, return_event_id, return_at, previous_activity_at, inactivity_days.
For each customer, identify the first completed purchase and the first completed purchase after it. Same-time purchases are sequenced by purchase_id. Return customers whose first repeat occurs within 30 calendar days inclusive, with first_purchase_id, repeat_purchase_id and days_to_repeat. Cancelled purchases do not count. Return columns: customer_id, first_purchase_id, repeat_purchase_id, days_to_repeat.
Rank products within each category by total completed-sales revenue. Rank 1 is the highest distinct revenue; products tied at the third distinct revenue are all included. Return category, product_id, revenue and revenue_rank ordered by category, rank, product_id. Return columns: category, product_id, revenue, revenue_rank.
Showing the first 60 of 70 challenges in this learning path.
Browse and filter all challenges in the Practice Arena