SQL Mini Project and Top SQL Interview Questions for Data Science

This lesson ties everything together with one small project, followed by the questions interviewers ask most often.

Mini Project: Reading a Bookstore’s Sales

The question: How is a small online bookstore performing month by month, and which genre earns the most? The data sits in one table, bookstore_sales, with sale_id, sale_date, genre, price and quantity. It holds 12 sales from July to September 2026.

Step 1: Monthly revenue and growth

A CTE calculates monthly revenue, and LAG compares each month with the previous one.

WITH monthly AS (
    SELECT DATE_TRUNC('month', sale_date)::DATE AS month_start,
           SUM(price * quantity)                AS revenue
    FROM bookstore_sales
    GROUP BY month_start
)
SELECT month_start,
       revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month_start))
             / LAG(revenue) OVER (ORDER BY month_start), 1) AS growth_pct
FROM monthly;

Output:

Code Explanation:

• DATE_TRUNC('month', ...) rounds every sale date down to the first day of its month, so sales can be grouped by month.
• price * quantity calculates each sale's revenue, and SUM adds it up per month.
• LAG(revenue) brings in the previous month’s revenue, and the formula turns the difference into a percentage.
• July has no earlier month, so its growth is NULL.

The graph below shows the trend, with revenue growing every month.

Figure 3: SQL mini project chart showing monthly revenue and month-over-month growth

Step 2: Which genre earns the most

The chart shows each genre’s revenue and its share of the total.

SELECT genre,
       SUM(price * quantity) AS revenue,
       ROUND(100.0 * SUM(price * quantity)
             / SUM(SUM(price * quantity)) OVER (), 1) AS share_pct
FROM bookstore_sales
GROUP BY genre
ORDER BY revenue DESC;

Output:

Code Explanation:

• The inner SUM(price * quantity) runs once per genre because of GROUP BY.
• The outer SUM(...) OVER () is a window function with no partition, so it adds up all the genre totals to give the grand total of 7950.
• Dividing each genre’s revenue by that grand total gives its share. The shares add up to 100.1 only because of rounding.

The finding: revenue grew 30.8% and then 35.3% in consecutive months, and Fiction brings in almost half of it. That is a result you could hand to a manager in two lines.

Top SQL Interview Questions

1. What is the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping happens. HAVING filters the groups after aggregation, so it is the one you use with conditions like SUM(amount) > 10000.

2. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN keeps only the rows that have a match in both tables. LEFT JOIN keeps every row from the left table and fills the right side with NULL when no match exists.

3. What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts every row in the group. COUNT(column) counts only the rows where that column is not NULL, so the two can give different answers on the same data.

4. How do you check for NULL values?

Use IS NULL or IS NOT NULL. A comparison like column = NULL is never true, because NULL means an unknown value and cannot be equal to anything.

5. What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

ROW_NUMBER gives every row a unique number, even when values tie. RANK gives tied rows the same number and then skips ahead, so you get 1, 1, 3. DENSE_RANK also repeats the number for ties but does not skip, so you get 1, 1, 2.

6. How do you find duplicate rows in a table?

Group by the columns that should be unique, count the rows in each group, and keep the groups with HAVING COUNT(*) > 1. Those are the values that appear more than once.

7. What is the difference between a subquery and a CTE?

Both give you an intermediate result to work with. A CTE is written at the top with a name, can be used more than once in the same query, and reads much better when the logic has several steps.

8. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes the rows you choose with a WHERE condition. TRUNCATE removes every row but keeps the table. DROP removes the whole table, including its structure.

9. What is an index, and what is the cost of having one?

An index is a lookup structure that lets the database find rows without scanning the entire table, which speeds up reads. The cost is extra disk space and slower writes, since every insert, update, and delete must also maintain the index.

10. How do you find the second highest salary?

Rank the salaries in a CTE with DENSE_RANK() OVER (ORDER BY salary DESC), then select the rows where the rank equals 2. DENSE_RANK is the safe choice here because it handles two people sharing the top salary correctly.