SQL Patterns for Analytics: Running Total to NULL Handling
After SQL Fundamentals: Understanding Query Execution Order, the next question is how to turn business questions into reusable analytics queries. These patterns map retention, growth, funnel drop-off, churn, segmentation, and ranking questions to the right window function or SQL construct. In interviews, they matter because they show whether you can move from a metric request to a working query structure.
- Running Total / Cumulative Sum uses SUM(daily_revenue) OVER (ORDER BY order_date) for cumulative revenue over time.
- Rank Within Group uses RANK() OVER (PARTITION BY category ORDER BY revenue DESC) to find Top N per Category.
- Cohort Analysis assigns users to DATE_TRUNC('month', signup_date) and tracks retained users by months_since_signup.
- Year-over-Year Growth uses LAG(revenue, 4) OVER (ORDER BY year, quarter) for quarterly analysis.
- Funnel Analysis uses FIRST_VALUE and LAG to calculate pct_of_top and step_conversion.
- RFM Customer Segmentation uses recency_days, frequency, monetary_value, and NTILE(5) to score customers.
- NULL Handling patterns use COALESCE, IFNULL, and NULLIF for sparse fields, safe division, and COUNT differences.
Analytics SQL Pattern Playbook
SQL patterns become easier to apply when each one is tied to the business question it answers. The playbook below keeps the focus on the construct, the metric, and the named example used in the query.
Each pattern maps a common business question - retention, growth, funnel drop-off, churn, segmentation, or ranking - to the right window function or SQL construct.
Pattern 1: Running Total / Cumulative Sum
Running Total & 7-Day Rolling Average is used for cumulative revenue over time. The Flipkart daily sales pattern calculates daily_revenue, cumulative_revenue, and rolling_7d_avg ordered by order_date.
Cumulative revenue over time is shown through Flipkart daily sales. The query uses SUM(daily_revenue) OVER (ORDER BY order_date) for cumulative_revenue and AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for rolling_7d_avg, so the same daily_sales table supports both total trend and recent trend analysis.
Pattern 2: Rank Within Group
Rank Within Group is used for Top N per Category. The Myntra pattern finds the Top 3 products by revenue within each category using RANK() with PARTITION BY category and ORDER BY revenue DESC.
Top 3 products by revenue within each category is the Myntra example for Rank Within Group. The correct approach uses a CTE named ranked, assigns RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rk, and then filters SELECT * FROM ranked WHERE rk <= 3.
Pattern 3: Cohort Analysis - User Retention
Cohort Analysis tracks retention by signup month. The query creates a cohort_base with each user_id and DATE_TRUNC('month', signup_date) AS cohort_month, then joins it to monthly activity from user_events.
Cohort analysis: retention by signup month is shown for an Indian EdTech platform. The query groups users by cohort_month, calculates months_since_signup, and counts COUNT(DISTINCT ua.user_id) AS retained_users to show retention over time.
Pattern 4: Year-over-Year Growth
Year-over-Year Growth with LAG() is used for YoY revenue growth. The Nykaa quarterly analysis pattern compares current revenue with LAG(revenue, 4), which references the revenue from four quarters earlier.
YoY revenue growth is shown through Nykaa quarterly analysis. The query selects year, quarter, and revenue, then uses LAG(revenue, 4) OVER (ORDER BY year, quarter) AS prev_year_revenue to calculate yoy_growth_pct.
Pattern 5: Funnel Analysis
Funnel Analysis with Step-Level Conversion is used for conversion funnel analysis. The Razorpay checkout flow pattern calculates users, pct_of_top, and step_conversion by stage and stage_order.
Conversion funnel analysis is shown through the Razorpay checkout flow. The query compares every stage with the first stage using FIRST_VALUE and with the previous stage using LAG, so pct_of_top and step_conversion show funnel drop-off at each stage.
Pattern 6: RFM Customer Segmentation
RFM Customer Segmentation uses recency_days, frequency, and monetary_value. The e-commerce pattern with ₹ amounts first creates rfm_raw from completed orders, then creates rfm_scored using NTILE(5).
RFM Segmentation is shown for e-commerce with ₹ amounts. The query scores customers using NTILE(5) across recency_days, frequency, and monetary_value, then assigns customer_segment values such as Champion, Loyal, Promising, At Risk, and Lost.
Pattern 7: Churn Detection - Inactive Users
Churn Detection identifies users inactive > 30 days. The Paytm wallet pattern joins users to transactions, calculates last_transaction_date and days_inactive, then labels the user as Churned or Active.
Detect churned users is shown for inactive > 30 days in a Paytm wallet pattern. The query uses a LEFT JOIN from users to transactions, calculates DATEDIFF(CURRENT_DATE, MAX(t.transaction_date)) AS days_inactive, and labels users as Churned when days_inactive is greater than 30.
Pattern 8: NULL Handling Patterns
NULL Handling uses COALESCE, IFNULL, and NULLIF patterns. The query is framed as NULL handling best practices because Indian data often has sparse fields.
NULL handling best practices are shown for Indian data that often has sparse fields. The query uses COALESCE(phone_number, email, 'No contact info') for primary_contact, IFNULL(promo_discount, 0) for discount_applied, and NULLIF(order_count, 0) for safe division.
Conclusion
These analytics SQL patterns turn recurring business questions into reusable query structures: running total, ranking, cohort retention, YoY growth, funnel conversion, RFM segmentation, churn detection, and NULL handling. The core takeaway is to match the metric question to the correct window function or SQL construct before writing the query.
The common mistake is trying to filter directly on a window alias, as in WHERE revenue_rank <= 3. Use in CTE/subquery - can't filter on window alias directly, so the correct approach is to create a ranked CTE and then filter WHERE rk <= 3.