20 Must Visit SQL Questions For Interviews
Last Updated on February 19, 2026 by Editorial Team Author(s): Ananya Originally published on Towards AI. Q1. Find the total number of orders placed by a customer (101) in a day. Table: Order_Details cust_id | order_id | order_date Code: select date_trunc(‘day’, order_date) as day, cust_id as customers count(distinct order_id) as orders from Order_Details where cust_id = 101 group by 1 Q2. Find the Monthly Cart Conversion Rate for a store with store code ‘APJ’ Event can be add_to_cart or checkout — item is added to cart and then checked out. Cart Conversion Rate = Total Items Checked out / Total Items Added to Cart Table: stores store_code | item_id | event | event_time_utc Code: select date_trunc(‘day’, event_time_utc) as day, count(distinct case when event = ‘add_to_cart’ then item_id else null end)/ count(distinct case when event = ‘checkout’ then item_id else null end) as conversion_rate from stores where store_code = ‘APJ’ group by 1 Q3. Create a daily funnel of authentication events such that first column is — page_view, second column is password_collect, third column is signin_attempt and fourth column is signin_success Column event_name mapping to each of these activities — pageview, password_collect, signin_attempt, signin_success Table: auth_events user_id | event_name | event_time_utc Code: Select date_trunc(‘day’, event_time_utc) as day, count(distinct case when event_name = ‘pageview’ then user_id else null end) as page_view, count(distinct case when event_name = ‘password_collect’ then user_id else null end) as password_collect, count(distinct case when event_name = ‘sigin_attempt’ then user_id else null end ) as signin_attempt, count(distinct case when event_name = ‘signin_success’ then user_id else null end) as signin_success from auth_events group by 1 Q4. Get minimum, average and maximum sales for each month. **each row represents one order line item, this query works as intended. Table: Order_Details cust_id | order_id | order_date | units | price select date_trunc(‘month’, order_date) as month, sum(units*price) as total_sales, avg(units*price) as avg_sales, min(units*price) as min_sales, max(units*price) as max_sales from order_details group by 1 Q5. Give 3 ways to find duplicates in a table called order_details Table: Order_Details cust_id | order_id | order_date | units | price Method 1: SELECT cust_id, order_id, order_date, units, price, COUNT(*) AS cntFROM order_detailsGROUP BY cust_id, order_id, order_date, units, priceHAVING COUNT(*) > 1; Method 2: SELECT *FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY cust_id, order_id, order_date, units, price ORDER BY order_date ) AS rn FROM order_details) tWHERE rn > 1; Q6. Write a SQL query to return all customers and their total order amount, including customers who have never placed an order, but exclude rows where the final total amount is NULL. Skills: Order of Execution SELECT c.customer_id, c.name, SUM(o.amount) AS total_amountFROM customers cLEFT JOIN orders o ON c.customer_id = o.customer_idGROUP BY c.customer_id, c.nameHAVING SUM(o.amount) IS NOT NULL; *This highlights join happens before where clause Order of Execution : FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT Q7. Give the code to solve for below problem: Skills : Making useful intermediate tables using window functions Table : Seats *availability 1 means seat is empty grain — seat_id, movie_id, zone Output — Print the seat id if three consecutive seats are free. Approach : Lay Output -> Consider Edge Cases -> Create intermediate outputs if needed -> create queries for each step -> create final query a) Lay output b) e1 — can have following 3 seats empty(eg. seat 3) e2-can have preceding 3 seats empty(eg. seat 5) e3- can have consecutive left and right seat empty(eg. seat 4) c) create intermediate table d) Create query for above tables WITH free_seats AS ( SELECT *, seat_id — ROW_NUMBER() OVER ( PARTITION BY movie_id, zone ORDER BY seat_id ) AS grp FROM Seats WHERE availability = 1) e) To reach from c to a, we can take a cummulative sum with window being curr window -1 to curr window +1 and ensure the availability sum > 3 SELECT seat_idFROM free_seatsGROUP BY movie_id, zone, grp, seat_idHAVING sum(availability)>= 3; Q8. Could you solve above question purely using joins yes — we need to cover three edge cases and consider 3 self joins (3 tables, s1, s2 and s3). SELECT DISTINCT seat_idFROM ( SELECT s1.seat_id FROM Seats s1 JOIN Seats s2 ON s2.seat_id = s1.seat_id + 1 AND s2.movie_id = s1.movie_id AND s2.zone = s1.zone JOIN Seats s3 ON s3.seat_id = s1.seat_id + 2 AND s3.movie_id = s1.movie_id AND s3.zone = s1.zone WHERE s1.availability = 1 AND s2.availability = 1 AND s3.availability = 1 UNION ALL SELECT s1.seat_id + 1 FROM Seats s1 JOIN Seats s2 ON s2.seat_id = s1.seat_id + 1 AND s2.movie_id = s1.movie_id AND s2.zone = s1.zone JOIN Seats s3 ON s3.seat_id = s1.seat_id + 2 AND s3.movie_id = s1.movie_id AND s3.zone = s1.zone WHERE s1.availability = 1 AND s2.availability = 1 AND s3.availability = 1 UNION ALL SELECT s1.seat_id + 2 FROM Seats s1 JOIN Seats s2 ON s2.seat_id = s1.seat_id + 1 AND s2.movie_id = s1.movie_id AND s2.zone = s1.zone JOIN Seats s3 ON s3.seat_id = s1.seat_id + 2 AND s3.movie_id = s1.movie_id AND s3.zone = s1.zone WHERE s1.availability = 1 AND s2.availability = 1 AND s3.availability = 1) tORDER BY seat_id; Q9. You are given a sales table with below schema – sales( employee_id INT, sale_date DATE, sales_amount INT) For each sale record, display: 1. employee_id 2. sale_year 3. sales_amount 4. Total sales made by that employee across all time 5. Total Sales made up till now 5. Difference between the employee’s highest sale made ever and current sale Approach : Lay Output -> Consider Edge Cases -> Create intermediate outputs if needed -> create queries for each step -> create final query a) Output: employee_id | sale_year | sales_amount | Total_sales_made | Total_sales_made_till_now| Diff b) Direct question c) no intermediate table(IT) required d) no IT so no Query e) select employee_id, date_trunc(‘year’, sale_date), sale_amount, sum(sale_amount) over (partition by employee_id order by sale_date rows between unbounded preceding and unbounded following) as total_sales_made, sum(sale_amount) over (partition by employee_id order by sale_date rows between unbounded preceding and current row) […]















