Week 01

CASE WHEN, Conditional Aggregation & Pivots

Day 32 to 2.5 hrs

Several measures from the same rows

Suppose a report needs total payments, successful payments and failed payments side by side. A WHERE filter cannot isolate successes without also changing the total. Put the condition inside the aggregate instead.

This same approach handles monthly revenue columns, success rates and rules such as "at least one successful payment and no failures".

CASE inside an aggregate

CASE produces a value for each row. An aggregate such as SUM then combines those values within the group. Each output column can use a different condition while sharing the same input rows.

We will use these UPI payments across July and August:

transactions

txn_id	user_name	app	amount	status	month
1	Rahul	PhonePe	250	success	2026-07
2	Rahul	GPay	120	failed	2026-07
3	Sneha	PhonePe	800	success	2026-07
4	Sneha	Paytm	150	success	2026-08
5	Arjun	GPay	60	failed	2026-08
6	Arjun	GPay	400	success	2026-08
7	Divya	PhonePe	90	failed	2026-08
8	Rahul	Paytm	300	success	2026-08

The table has eight payments across three apps. Each row records an attempt, including failures.

Start with the total

First, count every payment attempt per app.

SQL

SELECT app, COUNT(*) AS total
FROM transactions
GROUP BY app
ORDER BY app;

app	total
GPay	3
Paytm	2
PhonePe	3

GPay has three attempts. To show how many succeeded alongside that total, we need a separate conditional count.

Step 2: label each row with a 1 or a 0

Before aggregating, look at what CASE WHEN does to a single row. It reads its branches top to bottom, and the first condition that is true decides the value. If no branch matches and there is an ELSE, the ELSE value is used.

SQL

SELECT txn_id, app, status,
       CASE WHEN status = 'success' THEN 1 ELSE 0 END AS is_success
FROM transactions
ORDER BY txn_id;

txn_id	app	status	is_success
1	PhonePe	success	1
2	GPay	failed	0
3	PhonePe	success	1
4	Paytm	success	1
5	GPay	failed	0
6	GPay	success	1
7	PhonePe	failed	0
8	Paytm	success	1

Nothing is grouped yet. Every row got a tally mark of 1 or 0, and the 1s are exactly the rows we care about.

Step 3: put the label inside SUM

Now group by app and add up those 1s and 0s. Inside the GPay group the two failed rows contribute 0 and the one success row contributes 1, so the sum is 1.

SQL

SELECT app,
       COUNT(*)                                            AS total,
       SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count
FROM transactions
GROUP BY app
ORDER BY app;

app	total	success_count
GPay	3	1
Paytm	2	2
PhonePe	3	2

Two numbers about the same group, counted two different ways, in one pass. COUNT(*) counted everything, SUM(CASE ...) counted only the successes.

Step 4: one column per condition, and you have a pivot

Add a second copy of the same idea for 'failed', and a third that totals amount instead of counting. The status values that used to be rows are now columns.

SQL

SELECT app,
       SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END)      AS success_count,
       SUM(CASE WHEN status = 'failed'  THEN 1 ELSE 0 END)      AS failed_count,
       SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) AS success_amount
FROM transactions
GROUP BY app
ORDER BY app;

app	success_count	failed_count	success_amount
GPay	1	2	400
Paytm	2	0	450
PhonePe	2	1	1050

THEN 1 counts rows, THEN amount totals money, and all three columns share one GROUP BY app. This is a pivot using conditional aggregation.

What happens when nothing matches?

Paytm's failed_count is the row to watch. It prints 0 only because of ELSE 0. Drop the ELSE and every Paytm row evaluates to NULL, which is SQL's word for "no value here".

SUM skips NULLs, and a SUM that has nothing left to add returns NULL, not 0. So the cell comes back empty.

SQL

SELECT app, SUM(CASE WHEN status = 'failed' THEN 1 END) AS failed_count
FROM transactions
GROUP BY app
ORDER BY app;

app	failed_count (no ELSE)	failed_count (with ELSE 0)
GPay	2	2
Paytm	(empty, NULL)	0
PhonePe	1	1

Paytm has no failures. Use ELSE 0 if the report needs a zero count rather than NULL.

COUNT(CASE WHEN status = 'failed' THEN 1 END) also gives the right answer, because COUNT ignores NULLs and counts only the 1s. Leave out ELSE, or use ELSE NULL, so non-matching rows are not counted. ELSE 0 would make COUNT include them.

one group of rows
CASE labels each row
SUM adds only the labelled ones
one column per condition
pivoted row

The Pattern

Choose the groups first, then define what each measure counts.

Decide the grouping key. It becomes the left-hand column of the output and the whole GROUP BY.
Write one aggregate per output column, each carrying its own CASE WHEN.
Use THEN <column> when the ask is a total, THEN 1 when the ask is a count, and use ELSE 0 when non-matching rows should contribute zero to SUM. For a conditional AVG, usually leave them as NULL so they do not enter the denominator.
Rows that belong in no bucket at all should go out in WHERE. WHERE runs before grouping, so throwing them out early is cheaper than a CASE that keeps returning 0 for them.
If the qualification itself depends on a bucket, for example "the app must have failed at least once", repeat the conditional count in HAVING. HAVING is the filter that runs after grouping, so it is the only place a group-level condition can go.

Dry Run. Here is the four-column query above with HAVING failed_count > 0 added, executed step by step. This traces the logical grouping and filtering steps; the database may use a different physical execution plan.

Step	What runs	Rows after this step
FROM	read `transactions`	8
WHERE	nothing to filter	8
GROUP BY	group by `app`	3 groups (GPay 3, Paytm 2, PhonePe 3)
Aggregate	evaluate CASE values and totals per app	GPay 1/2, Paytm 2/0, PhonePe 2/1
HAVING	keep groups with `failed_count > 0`	2 groups (Paytm dropped)
ORDER BY	sort by `app`	GPay, PhonePe

Paytm has two rows and both are successes, so its failed_count is 0 and the entire group disappears at the HAVING step. One pass over the table, no self-join, no UNION.

Check what you learned

You need "how many of this app's transactions failed" as a column beside the total transaction count. What goes in the SELECT?

Query Templates

Templates 1 to 3 use the transactions table from above. Template 4, and most of the variations after it, use a second table: the placement sheet offers, one row per offer.

offer_id	student	branch	company	ctc
1	Rahul	CSE	Zomato	18.0
2	Sneha	CSE	Google	44.0
3	Arjun	ECE	TCS	3.6
4	Divya	ECE	Infosys	6.5
5	Kiran	ECE	Qualcomm	22.0
6	Meera	MECH	Accenture	4.5
7	Vivek	CSE	Flipkart	12.0

ctc is in lakhs per year.

SQL

-- 1. Conditional count and conditional total in one pass.
SELECT app,
       COUNT(*)                                                 AS total,
       SUM(CASE WHEN status = 'failed'  THEN 1 ELSE 0 END)      AS failures,
       SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) AS success_amount
FROM transactions
GROUP BY app;

-- 2. Pivot months into columns: one SUM(CASE) per bucket, one GROUP BY.
SELECT user_name,
       SUM(CASE WHEN month = '2026-07' THEN amount ELSE 0 END) AS jul,
       SUM(CASE WHEN month = '2026-08' THEN amount ELSE 0 END) AS aug
FROM transactions
WHERE status = 'success'
GROUP BY user_name
ORDER BY user_name;

-- 3. Percentage of a group, safe against a zero denominator.
SELECT app,
       ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END)
             / NULLIF(COUNT(*), 0), 2) AS success_pct
FROM transactions
GROUP BY app;

-- 4. Bucket a value into bands, then count the bands.
SELECT CASE WHEN ctc >= 20 THEN 'Dream'
            WHEN ctc >= 10 THEN 'Core'
            WHEN ctc >= 5  THEN 'Mass'
            ELSE 'Below 5' END AS band,
       COUNT(*) AS students
FROM offers
GROUP BY band
ORDER BY MIN(ctc) DESC;

Template 2 has a trap worth seeing. Run it on our table and it returns three rows, not four.

user_name	jul	aug
Arjun	0	400
Rahul	250	300
Sneha	800	150

Divya is missing. Her only transaction failed, so WHERE status = 'success' removed her last row, so there is no remaining row from which to form her group.

If the statement says every user must appear in the output, move the condition into the CASE and drop the WHERE completely.

SQL

SELECT user_name,
       SUM(CASE WHEN month='2026-07' AND status='success' THEN amount ELSE 0 END) AS jul,
       SUM(CASE WHEN month='2026-08' AND status='success' THEN amount ELSE 0 END) AS aug
FROM transactions
GROUP BY user_name
ORDER BY user_name;

user_name	jul	aug
Arjun	0	400
Divya	0	0
Rahul	250	300
Sneha	800	150

Divya now appears with two zeros. This version retains every user present in transactions; users with no transaction rows at all would need a separate users table and a LEFT JOIN.

IMP
Output formatting: If a report requires exactly two decimal places, a formatted result would look like this:

Text

GPay|33.33
Paytm|100.00
PhonePe|66.67

ROUND(100.0 * ok / total, 2) prints Paytm as 100.0, not 100.00, because ROUND drops a trailing zero it does not need. When fixed decimal places are required as text, SQLite's printf('%.2f', x) provides them.

Add ORDER BY when the statement specifies a result order.

Variations

Variation 1 - Bucketing a number into bands

Template 4 runs on the offers sheet shown above the templates and gives:

band	students
Dream	2
Core	2
Mass	1
Below 5	2

Sneha and Kiran are Dream, Rahul and Vivek are Core, Divya alone is Mass, and Arjun and Meera fall below 5.

Branches are tested top to bottom and the first match wins, so bands must be written from the highest threshold downwards. Put WHEN ctc >= 5 THEN 'Mass' on the first line instead, and the same sheet collapses:

band	students (correct order)	students (Mass written first)
Dream	2	(row missing)
Core	2	(row missing)
Mass	1	5
Below 5	2	2

Sneha's 44 satisfies >= 5 on the very first branch, so she is labelled Mass and the Dream branch is never reached. Two whole rows vanish from the output.

ORDER BY MIN(ctc) DESC is the neat way to print bands in band order, since the band name itself is a string and alphabetical order would put Below 5 on top.

Variation 2 - Simple CASE vs searched CASE

The searched form tests a fresh condition on each branch, and each condition can mention any columns you like:

SQL

CASE WHEN a > b THEN 'up' WHEN a = b THEN 'flat' ELSE 'down' END

The simple form names one expression once, then compares it against constants:

SQL

CASE app WHEN 'GPay' THEN 1 ELSE 0 END

The simple form compares one expression for equality. Use the searched form for conditions involving >, AND or IS NULL, or whenever writing each condition explicitly is clearer.

Variation 3 - CASE without any aggregate

CASE can also label individual rows without grouping:

SQL

SELECT student, company,
       CASE WHEN ctc >= 10 THEN 'TRUE' ELSE 'FALSE' END AS is_dream
FROM offers
ORDER BY student;

student	company	is_dream
Arjun	TCS	FALSE
Divya	Infosys	FALSE
Kiran	Qualcomm	TRUE
Meera	Accenture	FALSE
Rahul	Zomato	TRUE
Sneha	Google	TRUE
Vivek	Flipkart	TRUE

Seven rows in, seven rows out. Nothing collapsed, one column was added.

Match the output spelling exactly as the statement writes it. 'TRUE' here is a string in capitals, not the boolean 1, and it differs from both True and a boolean value.

Variation 4 - Sorting by a rule: CASE in ORDER BY

Sometimes the statement asks for an order that is not any single column: "failed payments at the bottom", "in stock items first". For that you sort by a CASE expression. It produces one value per row, and ORDER BY will happily sort on a value you invented.

"Successful payments first, largest amount first; failed ones at the bottom in txn_id order":

SQL

SELECT txn_id, status, amount
FROM transactions
ORDER BY CASE WHEN status = 'failed' THEN 1 ELSE 0 END ASC,
         CASE WHEN status <> 'failed' THEN amount END DESC,
         txn_id ASC;

txn_id	status	amount
3	success	800
6	success	400
8	success	300
1	success	250
4	success	150
2	failed	120
5	failed	60
7	failed	90

Three sort keys, three jobs. The first is a 0 or 1 flag that pushes failed rows to the bottom. The second is deliberately NULL for those rows, so they take no part in the amount sort. The third breaks any remaining tie.

Look at the failed rows: 120, 60, 90 is not in amount order, and that is intended. Their second key is NULL, so only txn_id orders them: 2, 5, 7.

Variation 5 - Conditional aggregate in HAVING

"Users with at least one successful payment and no failed one" needs a check across each user's rows. Filtering individual payments by status would lose information needed for that check. Two conditional aggregates in HAVING let us test both conditions together. An existence subquery is another option, which we cover later.

SQL

SELECT user_name
FROM transactions
GROUP BY user_name
HAVING SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) >= 1
   AND SUM(CASE WHEN status = 'failed'  THEN 1 ELSE 0 END) =  0;

user_name
Sneha

Rahul and Arjun each have one failed payment so the second condition drops them, and Divya has no success at all so the first condition drops her. The same pattern works for customers with a purchase but no refund, or accounts with a login but no failed login.

Variation 6 - COALESCE, IFNULL and NULLIF

These three small functions clean up the NULLs that conditional aggregation keeps producing.

COALESCE(x, y) returns the first argument that is not NULL. So COALESCE(SUM(CASE WHEN status='failed' THEN amount END), 0) turns Paytm's empty cell into a proper 0.

IFNULL(x, y) is the two argument SQLite and MySQL spelling of the same thing. Postgres does not have it, so COALESCE is the more portable choice.

NULLIF(a, b) is the mirror image. It returns NULL when a = b and returns a otherwise, which is why x / NULLIF(y, 0) quietly gives NULL instead of throwing a divide by zero error.

Check what you learned

SUM(CASE WHEN app = 'GPay' THEN amount END) with no ELSE, evaluated for the Paytm group (rows 4 and 8). What comes back?

Common Mistakes

Omitting ELSE 0 from a conditional SUM. Paytm has no failed rows, so SUM(CASE WHEN status='failed' THEN 1 END) returns NULL. Use ELSE 0 when the required result is zero.
Filtering away rows needed by another measure. WHERE status='failed' changes both the failure count and COUNT(*). Put the condition inside CASE when the total must still include successful attempts.
Using COUNT with ELSE 0. COUNT counts both 1 and 0 because neither is NULL. Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) or COUNT(CASE WHEN ... THEN 1 END).
Integer division in a percentage. In SQLite, 100 * ok / total can discard the fractional part. Use 100.0 * ok / NULLIF(total, 0).
Overlapping bands in the wrong order. A value of 22 matches both >= 5 and >= 20. CASE takes the first match, so put the higher threshold first.
Comparing to NULL with equals. WHEN col = NULL does not match missing values. Use WHEN col IS NULL.
Ending a timestamp range at a plain date. For July timestamps, >= '2026-07-01' AND < '2026-08-01' includes the whole last day.
Treating rounding as fixed-width formatting. ROUND returns a number. In SQLite, printf('%.2f', x) returns text with exactly two decimal places when that is required.

Interview Notes

Why not run separate queries and UNION them? UNION stacks results vertically. Conditional aggregation puts the measures side by side and can calculate them in one grouped query. For performance, compare execution plans rather than assuming a fixed number of scans.

Why use CASE? It is standard SQL and supports several conditions in one expression. Some databases also provide IF-style functions, but their availability and syntax vary.

COALESCE, IFNULL or ISNULL? COALESCE is standard SQL and returns the first non-NULL argument. It accepts more than two arguments, so you can provide several fallbacks. IFNULL takes two arguments in SQLite and MySQL. SQL Server's ISNULL provides a fallback, while MySQL's ISNULL tests whether a value is NULL. Prefer COALESCE for portable queries, and keep the argument types compatible.

How do you avoid a divide by zero? Wrap the denominator in NULLIF(denominator, 0). The row then returns NULL instead of erroring out, and if the report needs a number rather than a blank you can wrap the whole thing in COALESCE and give it 0.

Does the order of WHEN branches matter? Yes. CASE returns the result for the first true condition. For overlapping salary bands such as >= 5 and >= 20, put the higher threshold first. Mutually exclusive conditions do not have this overlap problem. Do not rely on CASE to prevent every possible expression error across databases; some expressions may be evaluated before CASE chooses a result.

Can you GROUP BY a CASE expression? Yes, that is exactly how bucketing works. SQLite and MySQL also let you group by the alias you gave it, which is convenient. Repeating the expression or moving it into a subquery makes the grouping intent explicit across databases.

How would you unpivot, that is columns back into rows? UNION ALL with one SELECT per column is the portable answer. Some engines have a dedicated UNPIVOT, SQLite does not.

When would you pivot in the application instead of in SQL? When the bucket list is data dependent. A SQL pivot needs every bucket written out by hand at query time. Three UPI apps is fine, three thousand product ids is not, and at that point you fetch rows and pivot in Python or in the BI tool.

Where do window functions fit into this? They give you the row and its group total side by side without collapsing anything. Conditional aggregation collapses a group into one row, while SUM(...) OVER (PARTITION BY ...) keeps all the rows and adds the total as an extra column. We will use this distinction in Week 2.

Quick Test

Check what you learned

On the transactions table, which expression counts only the successful payments inside each app's group?

Week 01

CASE WHEN, Conditional Aggregation & Pivots

Day 32 to 2.5 hrs

Several measures from the same rows

This same approach handles monthly revenue columns, success rates and rules such as "at least one successful payment and no failures".

CASE inside an aggregate

CASE produces a value for each row. An aggregate such as SUM then combines those values within the group. Each output column can use a different condition while sharing the same input rows.

We will use these UPI payments across July and August:

transactions

txn_id	user_name	app	amount	status	month
1	Rahul	PhonePe	250	success	2026-07
2	Rahul	GPay	120	failed	2026-07
3	Sneha	PhonePe	800	success	2026-07
4	Sneha	Paytm	150	success	2026-08
5	Arjun	GPay	60	failed	2026-08
6	Arjun	GPay	400	success	2026-08
7	Divya	PhonePe	90	failed	2026-08
8	Rahul	Paytm	300	success	2026-08

The table has eight payments across three apps. Each row records an attempt, including failures.

Start with the total

First, count every payment attempt per app.

SQL

SELECT app, COUNT(*) AS total
FROM transactions
GROUP BY app
ORDER BY app;

app	total
GPay	3
Paytm	2
PhonePe	3

GPay has three attempts. To show how many succeeded alongside that total, we need a separate conditional count.

Step 2: label each row with a 1 or a 0

SQL

SELECT txn_id, app, status,
       CASE WHEN status = 'success' THEN 1 ELSE 0 END AS is_success
FROM transactions
ORDER BY txn_id;

txn_id	app	status	is_success
1	PhonePe	success	1
2	GPay	failed	0
3	PhonePe	success	1
4	Paytm	success	1
5	GPay	failed	0
6	GPay	success	1
7	PhonePe	failed	0
8	Paytm	success	1

Nothing is grouped yet. Every row got a tally mark of 1 or 0, and the 1s are exactly the rows we care about.

Step 3: put the label inside SUM

Now group by app and add up those 1s and 0s. Inside the GPay group the two failed rows contribute 0 and the one success row contributes 1, so the sum is 1.

SQL

SELECT app,
       COUNT(*)                                            AS total,
       SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count
FROM transactions
GROUP BY app
ORDER BY app;

app	total	success_count
GPay	3	1
Paytm	2	2
PhonePe	3	2

Two numbers about the same group, counted two different ways, in one pass. COUNT(*) counted everything, SUM(CASE ...) counted only the successes.

Step 4: one column per condition, and you have a pivot

Add a second copy of the same idea for 'failed', and a third that totals amount instead of counting. The status values that used to be rows are now columns.

SQL

SELECT app,
       SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END)      AS success_count,
       SUM(CASE WHEN status = 'failed'  THEN 1 ELSE 0 END)      AS failed_count,
       SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) AS success_amount
FROM transactions
GROUP BY app
ORDER BY app;

app	success_count	failed_count	success_amount
GPay	1	2	400
Paytm	2	0	450
PhonePe	2	1	1050

THEN 1 counts rows, THEN amount totals money, and all three columns share one GROUP BY app. This is a pivot using conditional aggregation.

What happens when nothing matches?

Paytm's failed_count is the row to watch. It prints 0 only because of ELSE 0. Drop the ELSE and every Paytm row evaluates to NULL, which is SQL's word for "no value here".

SUM skips NULLs, and a SUM that has nothing left to add returns NULL, not 0. So the cell comes back empty.

SQL

SELECT app, SUM(CASE WHEN status = 'failed' THEN 1 END) AS failed_count
FROM transactions
GROUP BY app
ORDER BY app;

app	failed_count (no ELSE)	failed_count (with ELSE 0)
GPay	2	2
Paytm	(empty, NULL)	0
PhonePe	1	1

Paytm has no failures. Use ELSE 0 if the report needs a zero count rather than NULL.

one group of rows
CASE labels each row
SUM adds only the labelled ones
one column per condition
pivoted row

The Pattern

Choose the groups first, then define what each measure counts.

Decide the grouping key. It becomes the left-hand column of the output and the whole GROUP BY.
Write one aggregate per output column, each carrying its own CASE WHEN.
Use THEN <column> when the ask is a total, THEN 1 when the ask is a count, and use ELSE 0 when non-matching rows should contribute zero to SUM. For a conditional AVG, usually leave them as NULL so they do not enter the denominator.
Rows that belong in no bucket at all should go out in WHERE. WHERE runs before grouping, so throwing them out early is cheaper than a CASE that keeps returning 0 for them.
If the qualification itself depends on a bucket, for example "the app must have failed at least once", repeat the conditional count in HAVING. HAVING is the filter that runs after grouping, so it is the only place a group-level condition can go.

Step	What runs	Rows after this step
FROM	read `transactions`	8
WHERE	nothing to filter	8
GROUP BY	group by `app`	3 groups (GPay 3, Paytm 2, PhonePe 3)
Aggregate	evaluate CASE values and totals per app	GPay 1/2, Paytm 2/0, PhonePe 2/1
HAVING	keep groups with `failed_count > 0`	2 groups (Paytm dropped)
ORDER BY	sort by `app`	GPay, PhonePe

Paytm has two rows and both are successes, so its failed_count is 0 and the entire group disappears at the HAVING step. One pass over the table, no self-join, no UNION.

Check what you learned

You need "how many of this app's transactions failed" as a column beside the total transaction count. What goes in the SELECT?

Query Templates

Templates 1 to 3 use the transactions table from above. Template 4, and most of the variations after it, use a second table: the placement sheet offers, one row per offer.

offer_id	student	branch	company	ctc
1	Rahul	CSE	Zomato	18.0
2	Sneha	CSE	Google	44.0
3	Arjun	ECE	TCS	3.6
4	Divya	ECE	Infosys	6.5
5	Kiran	ECE	Qualcomm	22.0
6	Meera	MECH	Accenture	4.5
7	Vivek	CSE	Flipkart	12.0

ctc is in lakhs per year.

SQL

-- 1. Conditional count and conditional total in one pass.
SELECT app,
       COUNT(*)                                                 AS total,
       SUM(CASE WHEN status = 'failed'  THEN 1 ELSE 0 END)      AS failures,
       SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) AS success_amount
FROM transactions
GROUP BY app;

-- 2. Pivot months into columns: one SUM(CASE) per bucket, one GROUP BY.
SELECT user_name,
       SUM(CASE WHEN month = '2026-07' THEN amount ELSE 0 END) AS jul,
       SUM(CASE WHEN month = '2026-08' THEN amount ELSE 0 END) AS aug
FROM transactions
WHERE status = 'success'
GROUP BY user_name
ORDER BY user_name;

-- 3. Percentage of a group, safe against a zero denominator.
SELECT app,
       ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END)
             / NULLIF(COUNT(*), 0), 2) AS success_pct
FROM transactions
GROUP BY app;

-- 4. Bucket a value into bands, then count the bands.
SELECT CASE WHEN ctc >= 20 THEN 'Dream'
            WHEN ctc >= 10 THEN 'Core'
            WHEN ctc >= 5  THEN 'Mass'
            ELSE 'Below 5' END AS band,
       COUNT(*) AS students
FROM offers
GROUP BY band
ORDER BY MIN(ctc) DESC;

Template 2 has a trap worth seeing. Run it on our table and it returns three rows, not four.

user_name	jul	aug
Arjun	0	400
Rahul	250	300
Sneha	800	150

Divya is missing. Her only transaction failed, so WHERE status = 'success' removed her last row, so there is no remaining row from which to form her group.

If the statement says every user must appear in the output, move the condition into the CASE and drop the WHERE completely.

SQL

SELECT user_name,
       SUM(CASE WHEN month='2026-07' AND status='success' THEN amount ELSE 0 END) AS jul,
       SUM(CASE WHEN month='2026-08' AND status='success' THEN amount ELSE 0 END) AS aug
FROM transactions
GROUP BY user_name
ORDER BY user_name;

user_name	jul	aug
Arjun	0	400
Divya	0	0
Rahul	250	300
Sneha	800	150

Divya now appears with two zeros. This version retains every user present in transactions; users with no transaction rows at all would need a separate users table and a LEFT JOIN.

IMP
Output formatting: If a report requires exactly two decimal places, a formatted result would look like this:

Text

GPay|33.33
Paytm|100.00
PhonePe|66.67

Add ORDER BY when the statement specifies a result order.

Variations

Variation 1 - Bucketing a number into bands

Template 4 runs on the offers sheet shown above the templates and gives:

band	students
Dream	2
Core	2
Mass	1
Below 5	2

Sneha and Kiran are Dream, Rahul and Vivek are Core, Divya alone is Mass, and Arjun and Meera fall below 5.

band	students (correct order)	students (Mass written first)
Dream	2	(row missing)
Core	2	(row missing)
Mass	1	5
Below 5	2	2

Sneha's 44 satisfies >= 5 on the very first branch, so she is labelled Mass and the Dream branch is never reached. Two whole rows vanish from the output.

ORDER BY MIN(ctc) DESC is the neat way to print bands in band order, since the band name itself is a string and alphabetical order would put Below 5 on top.

Variation 2 - Simple CASE vs searched CASE

The searched form tests a fresh condition on each branch, and each condition can mention any columns you like:

SQL

CASE WHEN a > b THEN 'up' WHEN a = b THEN 'flat' ELSE 'down' END

The simple form names one expression once, then compares it against constants:

SQL

CASE app WHEN 'GPay' THEN 1 ELSE 0 END

The simple form compares one expression for equality. Use the searched form for conditions involving >, AND or IS NULL, or whenever writing each condition explicitly is clearer.

Variation 3 - CASE without any aggregate

CASE can also label individual rows without grouping:

SQL

SELECT student, company,
       CASE WHEN ctc >= 10 THEN 'TRUE' ELSE 'FALSE' END AS is_dream
FROM offers
ORDER BY student;

student	company	is_dream
Arjun	TCS	FALSE
Divya	Infosys	FALSE
Kiran	Qualcomm	TRUE
Meera	Accenture	FALSE
Rahul	Zomato	TRUE
Sneha	Google	TRUE
Vivek	Flipkart	TRUE

Seven rows in, seven rows out. Nothing collapsed, one column was added.

Match the output spelling exactly as the statement writes it. 'TRUE' here is a string in capitals, not the boolean 1, and it differs from both True and a boolean value.

Variation 4 - Sorting by a rule: CASE in ORDER BY

"Successful payments first, largest amount first; failed ones at the bottom in txn_id order":

SQL

SELECT txn_id, status, amount
FROM transactions
ORDER BY CASE WHEN status = 'failed' THEN 1 ELSE 0 END ASC,
         CASE WHEN status <> 'failed' THEN amount END DESC,
         txn_id ASC;

txn_id	status	amount
3	success	800
6	success	400
8	success	300
1	success	250
4	success	150
2	failed	120
5	failed	60
7	failed	90

Look at the failed rows: 120, 60, 90 is not in amount order, and that is intended. Their second key is NULL, so only txn_id orders them: 2, 5, 7.

Variation 5 - Conditional aggregate in HAVING

SQL

SELECT user_name
FROM transactions
GROUP BY user_name
HAVING SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) >= 1
   AND SUM(CASE WHEN status = 'failed'  THEN 1 ELSE 0 END) =  0;

user_name
Sneha

Variation 6 - COALESCE, IFNULL and NULLIF

These three small functions clean up the NULLs that conditional aggregation keeps producing.

COALESCE(x, y) returns the first argument that is not NULL. So COALESCE(SUM(CASE WHEN status='failed' THEN amount END), 0) turns Paytm's empty cell into a proper 0.

IFNULL(x, y) is the two argument SQLite and MySQL spelling of the same thing. Postgres does not have it, so COALESCE is the more portable choice.

NULLIF(a, b) is the mirror image. It returns NULL when a = b and returns a otherwise, which is why x / NULLIF(y, 0) quietly gives NULL instead of throwing a divide by zero error.

Check what you learned

SUM(CASE WHEN app = 'GPay' THEN amount END) with no ELSE, evaluated for the Paytm group (rows 4 and 8). What comes back?

Common Mistakes

Omitting ELSE 0 from a conditional SUM. Paytm has no failed rows, so SUM(CASE WHEN status='failed' THEN 1 END) returns NULL. Use ELSE 0 when the required result is zero.
Filtering away rows needed by another measure. WHERE status='failed' changes both the failure count and COUNT(*). Put the condition inside CASE when the total must still include successful attempts.
Using COUNT with ELSE 0. COUNT counts both 1 and 0 because neither is NULL. Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) or COUNT(CASE WHEN ... THEN 1 END).
Integer division in a percentage. In SQLite, 100 * ok / total can discard the fractional part. Use 100.0 * ok / NULLIF(total, 0).
Overlapping bands in the wrong order. A value of 22 matches both >= 5 and >= 20. CASE takes the first match, so put the higher threshold first.
Comparing to NULL with equals. WHEN col = NULL does not match missing values. Use WHEN col IS NULL.
Ending a timestamp range at a plain date. For July timestamps, >= '2026-07-01' AND < '2026-08-01' includes the whole last day.
Treating rounding as fixed-width formatting. ROUND returns a number. In SQLite, printf('%.2f', x) returns text with exactly two decimal places when that is required.

Interview Notes

Why use CASE? It is standard SQL and supports several conditions in one expression. Some databases also provide IF-style functions, but their availability and syntax vary.

How would you unpivot, that is columns back into rows? UNION ALL with one SELECT per column is the portable answer. Some engines have a dedicated UNPIVOT, SQLite does not.

Quick Test

Check what you learned

On the transactions table, which expression counts only the successful payments inside each app's group?