PolicyBazaar hard HAVINGCOUNT(DISTINCT)GROUP BYCOALESCE

For each customer_name in policybazaar_purchases, count the number of DISTINCT policy_category values they've purchased and sum their premium_amount (using 0 for a customer whose premiums are entirely unconfirmed, instead of NULL). Return only customers with at least 2 distinct categories — a customer with 2 purchases in the same category doesn't count as cross-sell. Return customer_name, distinct_categories, and total_premium, sorted by customer_name.

PolicyBazaar · Cross-Sell Policy Analysis — practice this real-world SQL scenario live in your browser.

📖 Story PolicyBazaar · Cross-Sell Policy Analysis

Monday morning at PolicyBazaar's Cross-Sell Analytics team. policybazaar_purchases logs one row per policy a customer has bought, with policy_category ('Health', 'Motor', 'Life', 'Travel') and the premium_amount they paid. A cross-sell success is a customer who has bought at least 2 different kinds of policy, not just 2 policies — someone who bought two separate Health policies from two different insurers is still only a Health customer, not a cross-sell win. A handful of purchases also have premium_amount = NULL, because the policy has been issued but the payment gateway hasn't confirmed the premium yet — it's a genuine 'we don't have that number yet,' not a premium of zero. The team wants every cross-sell customer along with how many distinct categories they hold and their total premium.

🎯 Your Mission

📋 Table Structure

🗂 policybazaar_purchases

purchase_id INTEGER 1

customer_name TEXT Ananya Rao

policy_category TEXT Health

premium_amount REAL 12000

⚡ Step-by-Step Walkthrough

Look at every purchase, one row per policy bought

query.sql

SELECT *
FROM policybazaar_purchases
ORDER BY customer_name, purchase_id;

💡 Explanation

policybazaar_purchases has 12 rows across 6 customers — Ananya Rao, Bharath Iyer, Chitra Menon, and Devika Nair each show up twice, Farah Sheikh three times, and only Esha Kapoor has just one purchase.
Bharath Iyer's two rows are both policy_category = 'Health' — two purchases, but the same category both times, bought from two different insurers he compared on the platform.
Devika Nair's two rows both have premium_amount = NULL — both her policies (Health and Motor) were issued through PolicyBazaar, but the payment gateway hasn't confirmed either premium yet, so there's genuinely no number to report for her at all right now.
Chitra Menon has one NULL premium out of two purchases (Travel is pending, Life already paid) — that's a partial NULL, different from Devika's case where every single one of her purchases is still unconfirmed.

See the trap: COUNT() without DISTINCT treats repeat purchases as cross-sell

query.sql

SELECT customer_name,
       COUNT(policy_category) AS category_count_wrong,
       SUM(premium_amount) AS total_premium
FROM policybazaar_purchases
GROUP BY customer_name
HAVING COUNT(policy_category) >= 2
ORDER BY customer_name;

💡 Explanation

This flags Bharath Iyer as a cross-sell success with category_count_wrong = 2, even though every one of his purchases is a Health policy — COUNT(policy_category) counts non-NULL rows, not distinct values, so two Health purchases count exactly the same as one Health and one Motor purchase.
Ananya Rao, Chitra Menon, and Farah Sheikh happen to get the right answer here too, but only by coincidence — none of them has a repeated category, so counting rows and counting distinct categories give the same number for them.
Devika Nair's total_premium shows up as NULL in this result, not 0 — SUM(premium_amount) has nothing but NULLs to add for her, and SUM over an all-NULL group returns NULL, which looks like missing data rather than a genuine zero-so-far.
Both problems pass silently: nothing errors, and the row count even looks reasonable — the only way to catch Bharath being wrongly included is to actually look at which category he bought, not just how many rows he has.

Fix it: COUNT(DISTINCT ...) plus COALESCE for the all-NULL group

query.sql

SELECT customer_name,
       COUNT(DISTINCT policy_category) AS distinct_categories,
       COALESCE(SUM(premium_amount), 0) AS total_premium
FROM policybazaar_purchases
GROUP BY customer_name
HAVING COUNT(DISTINCT policy_category) >= 2
ORDER BY customer_name;

💡 Explanation

COUNT(DISTINCT policy_category) counts unique category values only, so Bharath Iyer's two Health purchases collapse into 1 distinct category — he correctly drops out of the HAVING COUNT(DISTINCT policy_category) >= 2 filter entirely.
Four customers come back now — Ananya Rao, Chitra Menon, Devika Nair, and Farah Sheikh — the customers who genuinely hold at least two different kinds of policy.
Devika Nair's total_premium now shows 0 instead of NULL, because COALESCE(SUM(premium_amount), 0) only kicks in when SUM itself comes back NULL — for every other customer here, at least one real premium exists, so SUM already works and COALESCE never has to intervene.
Esha Kapoor and Bharath Iyer are both correctly absent from this result, but for different reasons — Esha genuinely has only one policy, while Bharath has two policies that happen to be the same kind; COUNT(DISTINCT ...) is what tells those two apart, plain COUNT(...) never could.

▶ Practice this live ← Browse all questions