SQL
Seventy questions on one realistic database, run on a real PostgreSQL engine inside your browser.
Every question uses the same Campus Store database (products, customers, sales), so you spend your time on SQL, not on learning a new schema each time. Run checks your query on the sample data; Submit also runs it on a hidden dataset with different values, so hard-coded answers don't pass.
- Write SELECT, WHERE, ORDER BY, GROUP BY and HAVING without looking anything up
- Join tables correctly, including left joins and "find what's missing" questions
- Use subqueries, CASE, window functions and CTEs — the usual interview filter
- Change data safely with INSERT, UPDATE, constraints, views and transactions
Reading a table
SELECT, WHERE, AND / OR, BETWEEN and ORDER BY.
Reading a table
SELECT, WHERE and ORDER BY: picking columns, filtering rows and sorting them.
The whole sales register
The store manager wants to scroll through the complete sales register.
A simple price list
Print a price list for the notice board.
When did each sale happen?
Accounts wants a list of bill numbers and their dates.
Big-ticket bills
Which bills were large? Return all columns of the sales whose total_price is more than ₹5,000.
The stationery shelf
Return all columns of the products in the Stationery category.
One Saturday's bills
The shop had a stall at a campus fest on 9 March 2024.
Premium products
Return product_id and product_name of the products whose unit_price is above ₹1,500.
Bulk buys
Someone buying more than 4 units in one go counts as a bulk buy.
Most expensive first
Return product_name and unit_price of every product, sorted from the highest price to the lowest.
Mid-range products
Return product_name and unit_price of the products priced from ₹100 to ₹2,000, both included.
Summarising data
COUNT, SUM, AVG, GROUP BY, DISTINCT, LIMIT and dates.
Summarising data
Aggregate functions, GROUP BY, rounding, DISTINCT, LIMIT and formatting dates.
Grouped by aisle
Return product_name and category of every product, sorted by category A→Z, and inside each category by product name A→Z.
Total revenue
How much money has the store taken in, in total?
Average shelf price
Return the average unit_price of all products, rounded to 2 decimal places.
Units moved
Return the total number of units sold across all sales (one value).
Adding GST
Bills are stored before tax. For every sale return sale_id, total_price, and price_with_gst: the total price plus 18% GST, rounded to 2 decimal places.
Typical bill size
Return the average total_price of a sale, rounded to 2 decimal places (one value).
Sales per day
For each date on which something was sold, return sale_date and sales_count (how many sales happened that day), earliest date first.
The priciest product
Return product_name and unit_price of the single most expensive product.
Receipt-style dates
Receipts print dates like 06-Jan-2024.
Which aisles exist?
Return each category once, sorted A→Z.
Joining tables
Inner and left joins, LIKE and finding what's missing.
Joining tables
Combining sales with products and customers: inner joins, left joins, LIKE and finding what's missing.
Name the product on every sale
The sales table only stores product_id. Return sale_id and product_name for every sale.
Electronics revenue
Return the total revenue (sum of total_price) from sales of products in the Electronics category, as one value.
Stationery units
Return the total quantity_sold of products in the Stationery category (one value).
List value vs what was paid
Some bills got a discount. For every sale return sale_id, product_name, list_value (= quantity_sold × the product's unit_price) and total_price (what was actually paid).
Anything “Smart”
Return sale_id, product_name and total_price of the sales of products whose name contains Smart.
How many at a time, for pricey items?
Return the average quantity_sold of sales of products whose unit_price is above ₹1,000, rounded to 2 decimal places (one value).
Full receipt lines
Return sale_id, customer_name, product_name and sale_date for every sale, sorted by sale_id.
Popularity count
For every product, including those never sold, return product_name and sales_count: how many sales it appears in (0 if none).
Dead stock
Return product_id and product_name of the products that have never been sold.
Window shoppers
Return customer_name and city of the customers who have never bought anything.
Groups, conditions & dates
HAVING, sorting by aggregates, conditional counts, months.
Groups, conditions and dates
GROUP BY after a join, HAVING, sorting by an aggregate, conditional counts and working with months.
Revenue by product
For each product that has been sold, return product_name and total_revenue (the sum of its sales' total_price).
Revenue by category
For each category that has sales, return category and total_revenue.
Best seller by number of bills
Which product appears in the most sales? Return its product_id and sales_count (one row).
The priciest aisle
Return the category with the highest average unit_price (just that one category name).
Products that crossed ₹3,000
Return product_name of the products whose total revenue (sum of total_price over their sales) is more than ₹3,000.
Sales per month
Return year, month (as numbers) and sales_count for every month that has sales, sorted by year, then month.
Month labels
Return sale_month as text like 2024-01, and sales_count, for every month that has sales, sorted by sale_month.
2024 revenue by category
Return category and total_revenue from sales made in the year 2024 only (categories with 2024 sales).
Spending by city
Return city and total_spent (the sum of total_price of all sales to customers from that city), highest total first. Only cities whose customers bought something.
Premium vs budget, per aisle
For every category return category, premium_count (products priced above ₹1,000) and budget_count (products priced ₹1,000 or less).
Subqueries & CASE
Queries inside queries, correlated subqueries and CASE.
Subqueries and CASE
Queries inside queries, correlated subqueries, CASE labels, date arithmetic and GREATEST.
Above-average quantities
Return all columns of the sales whose quantity_sold is greater than the average quantity_sold of all sales.
Above their own average
Return sale_id, customer_id and total_price of the sales worth more than that same customer's average sale.
Fast movers
Return product_name, category and unit_price of the products whose total quantity sold (over all their sales) is greater than the average quantity_sold per sale.
Bill size bands
For every sale return sale_id, total_price and bill_band:
Days before quarter end
The quarter closes on 31 March 2024. For every sale return sale_id, sale_date and days_left: the number of days from the sale date to 31 March 2024 (a sale on 31 March would have 0).
Weekday or weekend?
For every sale return sale_id, sale_date and day_type: Weekend if it was a Saturday or Sunday, otherwise Weekday.
Electronics buyers
Return the customer_name of every customer who has bought at least one Electronics product, each name once.
The biggest bill
Return all columns of the sale (or sales, if tied) with the highest total_price.
Top product in each aisle
For each category return category, product_name and unit_price of its most expensive product.
Minimum bill
The store has a minimum bill of ₹500. For every sale return sale_id, total_price and amount_billed: the larger of total_price and 500.
Window functions & CTEs
RANK, running totals, LAG, pivots and WITH.
Window functions and CTEs
Ranking, running totals, shares of a total, first/last per group, LAG, pivots and WITH.
Revenue leaderboard
For each product that has been sold, return product_name, total_revenue and revenue_rank (1 = highest revenue, using RANK()), sorted by rank.
Running total per category
For every sale return category, product_name, sale_date and running_total: the category's revenue from its first sale up to and including this sale's date.
Share of revenue by category
For each category with sales return category, category_revenue and revenue_pct: its share of all revenue as a percentage, rounded to 2 decimals. Biggest share first.
Top 3 revenue contributors
Return product_name, total_revenue and revenue_pct (share of all revenue, rounded to 2 decimals) for the 3 products with the biggest share, biggest first.
First purchase of each customer
For each customer who bought something, return customer_name, product_name and sale_date of their earliest purchase.
Latest purchase of each customer
For each customer who bought something, return customer_name, sale_date and total_price of their most recent sale.
Quarter at a glance
Turn monthly revenue into columns. For each category with sales in January–March 2024, return category, jan, feb, mar: that category's revenue in each month (0 when it sold nothing that month).
Price rank inside each aisle
For every product return category, product_name, unit_price and price_rank: its position by price within its own category (1 = most expensive), using DENSE_RANK().
Month over month
Return sale_month (text like 2024-01), revenue (that month's total) and prev_revenue (the previous month's revenue; NULL for the first month), sorted by sale_month.
Big spenders
Return customer_name and total_spent of the customers whose total spend is above the average total spend of customers who bought something.
Changing data & structure
INSERT, UPDATE, views, constraints, indexes, transactions, procedures.
Changing data and structure
INSERT, UPDATE, views, constraints, indexes, transactions and stored procedures.
A name with an apostrophe
Add a new product: id 114, name Teacher's Diary, category Stationery, price ₹180.00.
Clearance on the cheapest three
Cut the unit_price of the 3 cheapest products by 10%, rounded to 2 decimals. Leave every other product unchanged.
A view of sales per product
Create a view named total_sales that shows, for each product that has been sold, product_name, category and total_sales_amount (the sum of its sales' total_price).
A view of the top sellers
Create a view named top_products listing the 3 products with the most units sold: product_name and total_quantity_sold.
A product dashboard view
Create a view named product_sales_info with, for every product (sold or not): product_id, product_name, category, unit_price and total_sales (the number of sales it appears in).
No zero-quantity sales
Add a CHECK constraint named chk_quantity_sold to sales so that quantity_sold must always be greater than zero.
Speeding up date lookups
Looking up a day's sales is the most common query at the counter. Create an index on sales(sale_date), then return all columns of the sales made on 9 March 2024.
Sell and reprice, together
In one transaction:
New product, first sale
In one transaction (both or neither):
A price-update procedure
Create a stored procedure update_unit_price(p_product_id INT, p_new_price NUMERIC) that sets that product's unit_price to the new price.