Business Case Problems

1. Find Churned Customers (No purchase in 60 days).

SELECT user_id 
FROM users 
WHERE last_purchase_date < CURRENT_DATE - INTERVAL '60 days';

2. Compute D7 Retention.

Logic: Percentage of users who signed up on ‘Day 0’ and were active on ‘Day 7’.

WITH Activity AS (
    SELECT user_id, signup_date, activity_date,
           DATEDIFF(activity_date, signup_date) as day_diff
    FROM UserActivity
)
SELECT signup_date,
       COUNT(DISTINCT user_id) as total_users,
       COUNT(DISTINCT CASE WHEN day_diff = 7 THEN user_id END) as retained_users,
       (COUNT(DISTINCT CASE WHEN day_diff = 7 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id)) as retention_rate
FROM Activity
GROUP BY signup_date;

1. Compute LTV (Lifetime Value) per Customer.

SELECT customer_id, SUM(amount) as total_revenue
FROM orders 
WHERE status = 'completed'
GROUP BY customer_id;

2. Revenue excluding refunds.

SELECT SUM(o.amount) - COALESCE(SUM(r.refund_amount), 0) as net_revenue
FROM orders o
LEFT JOIN refunds r ON o.id = r.order_id
WHERE o.status = 'completed';

1. Funnel Conversion (Visit -> Signup -> Purchase).

SELECT 
    COUNT(DISTINCT visit_id) as total_visits,
    COUNT(DISTINCT signup_id) as total_signups,
    COUNT(DISTINCT purchase_id) as total_purchases,
    (COUNT(DISTINCT signup_id) * 100.0 / COUNT(DISTINCT visit_id)) as visit_to_signup,
    (COUNT(DISTINCT purchase_id) * 100.0 / COUNT(DISTINCT signup_id)) as signup_to_purchase
FROM FunnelTracking;

2. A/B Test Conversion Uplift.

SELECT test_group, 
       COUNT(*) as users, 
       SUM(converted) as conversions,
       AVG(converted) as conv_rate
FROM ab_test_results
GROUP BY test_group;

1. Deduplicate rows keeping only latest record.

WITH RankedRows AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) as rn
    FROM employees
)
DELETE FROM employees WHERE id IN (SELECT id FROM RankedRows WHERE rn > 1);

2. Find Orphan Rows (Orders with no valid user).

SELECT o.id 
FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
WHERE u.id IS NULL;