Interview Questions

Q7. Write a query to calculate a running total.

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Q8. Percentage of total revenue per category.

SELECT 
    product_category,
    SUM(amount) AS category_revenue,
    ROUND(
        SUM(amount) * 100.0 / (SELECT SUM(amount) FROM orders), 
        2
    ) AS pct_of_total
FROM orders
GROUP BY product_category
ORDER BY pct_of_total DESC;

Q9. Find customers who ordered in consecutive months.

WITH customer_months AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', order_date) AS order_month,
        LAG(DATE_TRUNC('month', order_date)) OVER (
            PARTITION BY customer_id ORDER BY order_date
        ) AS prev_month
    FROM orders
)
SELECT DISTINCT customer_id
FROM customer_months
WHERE order_month = prev_month + INTERVAL '1 month';

Q10. Create a pivot table: revenue by category per quarter.

SELECT 
    EXTRACT(QUARTER FROM order_date) AS quarter,
    SUM(CASE WHEN product_category = 'Technology' THEN amount ELSE 0 END) AS technology,
    SUM(CASE WHEN product_category = 'Furniture' THEN amount ELSE 0 END) AS furniture,
    SUM(CASE WHEN product_category = 'Supplies' THEN amount ELSE 0 END) AS supplies
FROM orders
GROUP BY EXTRACT(QUARTER FROM order_date)
ORDER BY quarter;

---

CHAPTER 12: SQL QUICK REFERENCE CHEAT SHEET

Execution Order (memorize!):

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

Common Mistakes to Avoid

Mistake	Why It's Wrong	Fix
WHERE SUM(amount) > 100	Can't use aggregate in WHERE	Use HAVING
WHERE column = NULL	NULL can't be compared with =	Use IS NULL
Using alias in WHERE	SELECT runs after WHERE	Repeat the expression or use subquery
SELECT city, SUM(amount) without GROUP BY	Non-aggregated column without GROUP BY	Add GROUP BY city
Forgetting ON in JOINs	Creates a Cartesian product	Always specify JOIN condition

---

Practice platforms: HackerRank SQL, LeetCode SQL 50, Mode SQL Tutorial. Do at least 30 problems before your interview.