# SQL for Data Analysis — Conditional Aggregation, Pivots, Percentages and Funnels

Source: https://skills101.in/learn/dbms-sql/sql-data-analysis — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-21.

Analyst and business-analyst interviews rarely ask "write a SELECT". They ask "show category-wise sales **per city as columns**", "what **share** of revenue came from each city?", "how many users dropped off **at each step**?". These are a handful of patterns built from GROUP BY and CASE. Learn the patterns once and you can answer most such questions.

```sql
CREATE TABLE orders (order_id INT PRIMARY KEY, customer VARCHAR(20), city VARCHAR(20), category VARCHAR(20), order_date DATE, amount INT, status VARCHAR(10));
INSERT INTO orders VALUES
 (1, 'Asha', 'Jaipur', 'Books', '2026-01-05', 450, 'delivered'), (2, 'Ravi', 'Pune', 'Electronics', '2026-01-09', 2200, 'delivered'),
 (3, 'Neha', 'Jaipur', 'Electronics', '2026-01-17', 1800, 'returned'), (4, 'Kabir', 'Delhi', 'Books', '2026-01-23', 300, 'delivered'),
 (5, 'Asha', 'Jaipur', 'Clothes', '2026-02-02', 900, 'delivered'), (6, 'Om', 'Pune', 'Books', '2026-02-11', 250, 'cancelled'),
 (7, 'Ravi', 'Pune', 'Clothes', '2026-02-14', 1200, 'delivered'), (8, 'Meera', 'Delhi', 'Electronics', '2026-02-20', 3100, 'delivered'),
 (9, 'Neha', 'Jaipur', 'Books', '2026-03-03', 600, 'delivered'), (10, 'Kabir', 'Delhi', 'Clothes', '2026-03-08', 700, 'returned'),
 (11, 'Asha', 'Jaipur', 'Electronics', '2026-03-15', 1500, 'delivered'), (12, 'Om', 'Pune', 'Clothes', '2026-03-28', 800, 'delivered');
```

## 1. Conditional aggregation: CASE inside SUM and COUNT

Put a CASE **inside** an aggregate to count or add only some rows — several different filters in **one** pass over the table.

```sql
SELECT city,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned,
       SUM(CASE WHEN status = 'delivered' THEN amount ELSE 0 END) AS revenue
FROM orders GROUP BY city ORDER BY revenue DESC;
```

```text
city | orders | delivered | returned | revenue
Pune | 4 | 3 | 0 | 4200
Jaipur | 5 | 4 | 1 | 3450
Delhi | 3 | 2 | 1 | 3400
```

Why not three separate queries with WHERE? Because each would scan the table again, and you would still have to combine the results. One GROUP BY with conditional sums gives one neat row per city.

`COUNT(CASE WHEN … THEN 1 END)` also works: with no ELSE, the CASE gives NULL, and COUNT skips NULLs. PostgreSQL and SQLite also accept the shorter `COUNT(*) FILTER (WHERE status = 'returned')`.

## 2. Pivot: rows into columns

**Long** data has one row per (city, category). A **pivot** turns the categories into columns — the shape managers want in a report.

![Pivot: long rows of city, category and revenue are turned into one row per city with a column for each category: Books, Clothes and Electronics](https://skills101.in/learn-img/dbms-sql/pivot.svg)

```sql
SELECT city,
       SUM(CASE WHEN category = 'Books' THEN amount ELSE 0 END) AS books,
       SUM(CASE WHEN category = 'Clothes' THEN amount ELSE 0 END) AS clothes,
       SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) AS electronics
FROM orders WHERE status = 'delivered'
GROUP BY city ORDER BY city;
```

```text
city | books | clothes | electronics
Delhi | 300 | 0 | 3100
Jaipur | 1050 | 900 | 1500
Pune | 0 | 2000 | 2200
```

It is the same conditional aggregation, with one CASE per output column. SQL Server and Oracle have a `PIVOT` keyword, but this CASE version works in **every** database, so interviewers usually expect it. Its limit: you must know the column values (Books, Clothes, Electronics) when you write the query.

**Unpivot** goes the other way — columns back into rows — with UNION ALL:

```sql
WITH wide AS (
    SELECT 'Jaipur' AS city, 1050 AS books, 900 AS clothes
)
SELECT city, 'Books' AS category, books AS amount FROM wide
UNION ALL
SELECT city, 'Clothes', clothes FROM wide;
```

```text
city | category | amount
Jaipur | Books | 1050
Jaipur | Clothes | 900
```

## 3. Share of total (percentages)

"What percentage of delivered revenue came from each city?" Divide each group by the grand total. A window function over the grouped result gives the total without a second query:

```sql
SELECT city, SUM(amount) AS revenue,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct
FROM orders WHERE status = 'delivered'
GROUP BY city ORDER BY revenue DESC;
```

```text
city | revenue | pct
Pune | 4200 | 38.0
Jaipur | 3450 | 31.2
Delhi | 3400 | 30.8
```

`SUM(SUM(amount)) OVER ()` looks odd but reads simply: the inner SUM is each city's total; the outer SUM with an empty `OVER ()` adds those totals across **all** groups. The `100.0` avoids integer division.

**Return rate** is a ratio inside each group:

```sql
SELECT category,
       ROUND(100.0 * SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) / COUNT(*), 1) AS return_pct
FROM orders GROUP BY category ORDER BY return_pct DESC, category;
```

```text
category | return_pct
Clothes | 25.0
Electronics | 25.0
Books | 0.0
```

## 4. Trends by month

Group by a **truncated date**. In SQLite `strftime('%Y-%m', d)`, in MySQL `DATE_FORMAT(d, '%Y-%m')`, in PostgreSQL `DATE_TRUNC('month', d)`.

```sql
SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'delivered' THEN amount ELSE 0 END) AS revenue
FROM orders GROUP BY month ORDER BY month;
```

```text
month | orders | revenue
2026-01 | 4 | 2950
2026-02 | 4 | 5200
2026-03 | 4 | 2900
```

Add `LAG` to get month-on-month growth:

```sql
WITH m AS (
    SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue
    FROM orders WHERE status = 'delivered' GROUP BY month
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM m ORDER BY month;
```

```text
month | revenue | growth_pct
2026-01 | 2950 | NULL
2026-02 | 5200 | 76.3
2026-03 | 2900 | -44.2
```

February grew 76.3% over January's 2950; March fell 44.2%, which shows as a negative number. The first month has no previous month, so its growth is NULL — not 0.

## 5. Subtotals and a grand total

MySQL, PostgreSQL, SQL Server and Oracle can add subtotal rows with `GROUP BY ROLLUP(city)`. A portable way that works everywhere (including SQLite) is UNION ALL of the detail and the total:

```sql
SELECT city, SUM(amount) AS revenue FROM orders WHERE status = 'delivered' GROUP BY city
UNION ALL
SELECT 'ALL CITIES', SUM(amount) FROM orders WHERE status = 'delivered';
```

```text
city | revenue
Delhi | 3400
Jaipur | 3450
Pune | 4200
ALL CITIES | 11050
```

## 6. Funnel: where do users drop off?

A learning website tracks steps: visit → sign up → take a test → pay. A **funnel** counts how many users reached each step and the conversion from the step before.

```sql
CREATE TABLE events (user_id INT, step VARCHAR(10));
INSERT INTO events VALUES (1, 'visit'), (2, 'visit'), (3, 'visit'), (4, 'visit'), (5, 'visit'), (6, 'visit'), (7, 'visit'), (8, 'visit'),
 (1, 'signup'), (2, 'signup'), (3, 'signup'), (5, 'signup'), (7, 'signup'),
 (1, 'test'), (2, 'test'), (5, 'test'), (1, 'paid'), (5, 'paid');

WITH f AS (
    SELECT COUNT(DISTINCT CASE WHEN step = 'visit' THEN user_id END) AS visit,
           COUNT(DISTINCT CASE WHEN step = 'signup' THEN user_id END) AS signup,
           COUNT(DISTINCT CASE WHEN step = 'test' THEN user_id END) AS test,
           COUNT(DISTINCT CASE WHEN step = 'paid' THEN user_id END) AS paid
    FROM events
)
SELECT visit, signup, test, paid,
       ROUND(100.0 * signup / visit, 1) AS signup_pct,
       ROUND(100.0 * paid / visit, 1) AS overall_pct
FROM f;
```

```text
visit | signup | test | paid | signup_pct | overall_pct
8 | 5 | 3 | 2 | 62.5 | 25.0
```

![Funnel bars computed from the events table: 8 visit, 5 sign up, 3 take a test and 2 pay, with the step-to-step conversion beside each bar](https://skills101.in/learn-img/dbms-sql/funnel.svg)

`COUNT(DISTINCT …)` matters: if a user takes two tests, they must still count once.

## Common mistakes

- Using `ELSE 0` inside `COUNT(CASE …)`. COUNT counts the 0s too — use SUM with ELSE 0, or COUNT with no ELSE.
- Integer division: `100 * 3 / 8` gives 37 in many databases. Write `100.0 *`.
- Mixing filters: putting `WHERE status = 'delivered'` in a query where you also want to count returned orders. Use CASE instead.
- Showing growth from 0 or NULL as 0%. If the previous value is missing, the growth is unknown.
- Counting events instead of users in a funnel. Use COUNT(DISTINCT user_id).

## Quick recap

- `SUM(CASE WHEN cond THEN x ELSE 0 END)` = many filtered totals in one pass.
- Pivot = one conditional SUM per output column; unpivot = UNION ALL.
- Share of total: `100.0 * SUM(x) / SUM(SUM(x)) OVER ()`.
- Month trends: group by a truncated date; growth with LAG.
- Subtotals: ROLLUP where supported, or UNION ALL a total row; funnels with COUNT(DISTINCT CASE …).


Practice quiz and free certificate: https://skills101.in/learn/dbms-sql/sql-data-analysis