🎯 𝐒𝐐𝐋 𝐒𝐞𝐫𝐢𝐞𝐬 | 𝐄𝐩. 𝟓 — 𝐅𝐢𝐥𝐭𝐞𝐫𝐢𝐧𝐠 & 𝐂𝐨𝐧𝐝𝐢𝐭𝐢𝐨𝐧𝐚𝐥 𝐋𝐨𝐠𝐢𝐜 𝐰𝐢𝐭𝐡 𝐂𝐀𝐒𝐄 𝐖𝐇𝐄𝐍

Data rarely fits into neat boxes.
Sometimes you need to teach SQL to decide for itself.
That’s where 𝐂𝐀𝐒𝐄 𝐖𝐇𝐄𝐍 comes in — your logic gate for turning raw data into decisions.

🧩 𝐋𝐞𝐭’𝐬 𝐒𝐞𝐞 𝐈𝐭 𝐢𝐧 𝐀𝐜𝐭𝐢𝐨𝐧:
𝐄𝐱𝐚𝐦𝐩𝐥𝐞: 𝐂𝐨𝐦𝐩𝐚𝐫𝐢𝐧𝐠 𝐏𝐚𝐲𝐢𝐧𝐠 𝐯𝐬. 𝐍𝐨𝐧-𝐏𝐚𝐲𝐢𝐧𝐠 𝐔𝐬𝐞𝐫𝐬
🔗 𝐏𝐫𝐞𝐦𝐢𝐮𝐦 𝐯𝐬 𝐅𝐫𝐞𝐞𝐦𝐢𝐮𝐦 — https://lnkd.in/e2_5mUpr

📊 𝐁𝐮𝐬𝐢𝐧𝐞𝐬𝐬 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧:
Find the total number of downloads for paying and non-paying users by date — but only show days when non-paying users had more downloads than paying users.

🧠 𝐒𝐭𝐞𝐩 𝟏: 𝐀𝐬𝐤 𝐭𝐡𝐞 𝐫𝐢𝐠𝐡𝐭 𝐝𝐚𝐭𝐚 𝐪𝐮𝐞𝐬𝐭𝐢𝐨𝐧:
1️⃣ What defines a paying user?
2️⃣ How do I calculate downloads by type?
3️⃣ How do I filter only when non-paying > paying?

💻 𝐒𝐭𝐞𝐩 𝟐: 𝐓𝐫𝐚𝐧𝐬𝐥𝐚𝐭𝐞 𝐢𝐧𝐭𝐨 𝐒𝐐𝐋 𝐥𝐨𝐠𝐢𝐜

select
date as download_date,
sum(case when paying_customer=’no’ then downloads else 0 end) as non_paying,
sum(case when paying_customer=’yes’ then downloads else 0 end) as paying
from
download_status ds group by download_date
having
sum(case when paying_customer=’no’ then downloads else 0 end) >
sum(case when paying_customer=’yes’ then downloads else 0 end)
order by download_date asc;

Get Somyagupta’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

🧩 𝐖𝐡𝐚𝐭’𝐬 𝐡𝐚𝐩𝐩𝐞𝐧𝐢𝐧𝐠 𝐡𝐞𝐫𝐞?
👉 𝐂𝐀𝐒𝐄 𝐖𝐇𝐄𝐍 classifies each download by payment type.
👉 𝐒𝐔𝐌 aggregates total downloads for both.
👉 𝐇𝐀𝐕𝐈𝐍𝐆 filters only dates where non-paying > paying.
👉 𝐎𝐑𝐃𝐄𝐑 𝐁𝐘 shows trends over time.

💡 This query doesn’t just retrieve data — it tells a story about user behavior.

🧠 𝐓𝐫𝐲 𝐈𝐭 𝐘𝐨𝐮𝐫𝐬𝐞𝐥𝐟:
If you liked this logic, try applying CASE WHEN in these problems:

1️⃣ 𝐌𝐞𝐭𝐚/𝐅𝐚𝐜𝐞𝐛𝐨𝐨𝐤 𝐀𝐜𝐜𝐨𝐮𝐧𝐭𝐬 — https://lnkd.in/e2XibwGW
Calculate the ratio of ‘closed’ accounts among all accounts with status records on January 10, 2020.
𝐇𝐢𝐧𝐭: Use CASE WHEN status = ‘closed’ THEN 1 ELSE 0 END inside an aggregation to calculate ratios.

2️⃣ 𝐀𝐩𝐩𝐥𝐞 𝐏𝐫𝐨𝐝𝐮𝐜𝐭 𝐂𝐨𝐮𝐧𝐭𝐬 — https://lnkd.in/e2cN2fDe
Measure the popularity of Apple devices (macbook pro, iphone 5s, ipad air) across languages.
𝐇𝐢𝐧𝐭: Use CASE WHEN device IN (…) THEN user_id END to flag Apple users before counting distinct users per language.

🧭 𝐓𝐚𝐤𝐞𝐚𝐰𝐚𝐲:
𝐂𝐀𝐒𝐄 𝐖𝐇𝐄𝐍 is where SQL starts thinking like an analyst.
It’s not just filtering — it’s reasoning with data.

💬 What’s one tricky conditional query you’ve solved recently? Share below — let’s discuss how to optimize it!

🔥 𝐂𝐨𝐦𝐢𝐧𝐠 𝐮𝐩 𝐧𝐞𝐱𝐭: 𝐄𝐩. 𝟔 — 𝐂𝐎𝐔𝐍𝐓, 𝐒𝐔𝐌, 𝐀𝐕𝐆, 𝐌𝐈𝐍, 𝐌𝐀𝐗 — 𝐌𝐞𝐚𝐬𝐮𝐫𝐢𝐧𝐠 𝐁𝐮𝐬𝐢𝐧𝐞𝐬𝐬 𝐌𝐞𝐭𝐫𝐢𝐜𝐬