Week 36 - SQL Chips and Chunks Newsletter

1. SQL Chip: Conditional Aggregation for Rates

To calculate a percentage like the renewal rate, you need to count specific outcomes (Renewed subscriptions) against the total pool of possibilities (Total subscriptions up for renewal). We use the SUM(CASE WHEN...) structure to count conditionally, treating TRUE as 1 and FALSE as 0. This is the most efficient and readable way to aggregate specific facts within a column.

2. SQL Chunk: Monthly Renewal Rate

This query calculates the raw counts of renewed versus churned subscriptions for the month of November 2025 for Enterprise clients.

SELECT
    DATE_TRUNC('month', s.end_date) AS renewal_month,
    CAST(SUM(CASE WHEN s.status = 'Renewed' THEN 1 ELSE 0 END) AS NUMERIC) AS renewed_count,
    COUNT(s.subscription_id) AS total_for_renewal,
    CAST(SUM(CASE WHEN s.status = 'Renewed' THEN 1 ELSE 0 END) AS NUMERIC) * 100.0 / COUNT(s.subscription_id) AS renewal_rate_pct
FROM
    Subscriptions s
JOIN
    Clients c ON s.client_id = c.client_id
WHERE
    c.client_type = 'Enterprise'
    -- We are looking at subscriptions whose end_date was in November 2025
    AND DATE_TRUNC('month', s.end_date) = '2025-11-01'
GROUP BY
    1;

3. SQL Challenge: Comparing Month-over-Month Renewal Rate

We can make this analysis much more powerful by comparing the current month's renewal rate to the previous month's. This uses a Window Function called LAG() to look back at the prior row's value, which in this case represents the prior month's rate.

WITH MonthlyRates AS (
    -- First, calculate the basic renewal rate for ALL months
    SELECT
        DATE_TRUNC('month', end_date) AS renewal_month,
        CAST(SUM(CASE WHEN status = 'Renewed' THEN 1 ELSE 0 END) AS NUMERIC) * 100.0 / COUNT(subscription_id) AS current_renewal_rate
    FROM
        Subscriptions
    WHERE
        client_type = 'Enterprise'
    GROUP BY
        1
)
SELECT
    renewal_month,
    current_renewal_rate,
    -- Use LAG to fetch the rate from the row immediately preceding the current one (the previous month)
    LAG(current_renewal_rate, 1) OVER (ORDER BY renewal_month) AS previous_month_rate
FROM
    MonthlyRates
ORDER BY
    renewal_month DESC;

4. SQL Mistake: The Integer Division Trap

One of the most common missteps in calculating percentages is forgetting to convert at least one of the numbers to a decimal or float type. If you divide one integer by another, SQL performs Integer Division, which truncates the decimal part, resulting in a whole number (often 0) instead of the actual percentage.

5. SQL in Practice: E-Commerce Subscription Health

A major e-commerce platform that offers premium software features (e.g., advanced analytics for sellers) needs to track this metric. Analyzing the monthly renewal rate by the Region where the enterprise client is based can reveal critical issues. If the APAC region has a consistently lower renewal rate than North America, it suggests there might be a problem with regional product localization, customer support hours, or a local competitor that needs immediate attention from the product team.

SELECT
    DATE_TRUNC('month', s.end_date) AS renewal_month,
    c.region AS client_region,
    COUNT(s.subscription_id) AS total_for_renewal,
    CAST(SUM(CASE WHEN s.status = 'Renewed' THEN 1 ELSE 0 END) AS NUMERIC) AS renewed_count,
    -- Calculate the percentage rate for each region in that month
    CAST(SUM(CASE WHEN s.status = 'Renewed' THEN 1 ELSE 0 END) AS NUMERIC) * 100.0 / COUNT(s.subscription_id) AS regional_renewal_rate_pct
FROM
    Subscriptions s
JOIN
    Clients c ON s.client_id = c.client_id
WHERE
    c.client_type = 'Enterprise'
    -- Focusing on Q3 2025 renewals for the E-Commerce scenario
    AND s.end_date >= '2025-07-01'
    AND s.end_date < '2025-10-01'
GROUP BY
    1, 2  -- Grouping by both month and region
ORDER BY
    renewal_month, client_region;

This query gives the product team the hard numbers they need.

6. SQL Resource: IBM Resource on Churn

• Resource Name: What is customer churn? | IBM

• Description: This resource from IBM provides the essential business context for our analysis. It defines customer churn, explains its critical importance in B2B SaaS (like software subscriptions), and provides the core calculation formula. This knowledge is crucial for a data professional, ensuring you apply advanced SQL techniques (like the LAG() window function used in our challenge query) to address the right business question and interpret the resulting time-series data correctly.