Objective

You are given a table of phone call records. Each row represents a single call with details about the caller, receiver, date, and duration in seconds.

Table Schema:

The phone_calls table:

Task Requirements:

• Filter for calls that last more than 5 minutes (300 seconds)
• Count the number of qualifying calls per month
• Calculate the month-over-month growth rate as a percentage
• Round the growth percentage to 1 decimal place
• The first month should show NULL for the growth rate since there is no previous month
• Order the results by year and month in ascending order

Output columns: year, month, growth_pct

WITH monthly_long_calls AS (
    SELECT
        EXTRACT(YEAR FROM call_date)::INT AS year,
        EXTRACT(MONTH FROM call_date)::INT AS month,
        COUNT(*) AS long_call_count
    FROM phone_calls
    WHERE duration_secs > 300
    GROUP BY EXTRACT(YEAR FROM call_date), EXTRACT(MONTH FROM call_date)
)
SELECT
    year,
    month,
    ROUND(
        (long_call_count - LAG(long_call_count) OVER (ORDER BY year, month))
        * 100.0
        / LAG(long_call_count) OVER (ORDER BY year, month)
    , 1) AS growth_pct
FROM monthly_long_calls
ORDER BY year, month;

Explanation

Step 1: Filter and aggregate long calls by month

The CTE filters for calls where duration_secs > 300 and groups them by year and month using EXTRACT(). The COUNT(*) gives the number of qualifying calls per month.

SELECT
    EXTRACT(YEAR FROM call_date)::INT AS year,
    EXTRACT(MONTH FROM call_date)::INT AS month,
    COUNT(*) AS long_call_count
FROM phone_calls
WHERE duration_secs > 300
GROUP BY EXTRACT(YEAR FROM call_date), EXTRACT(MONTH FROM call_date)

Step 2: Calculate month-over-month growth using LAG()

The LAG() window function retrieves the previous month's count, ordered by year and month. The growth percentage is calculated as (current - previous) * 100.0 / previous and rounded to 1 decimal place.

ROUND(
    (long_call_count - LAG(long_call_count) OVER (ORDER BY year, month))
    * 100.0
    / LAG(long_call_count) OVER (ORDER BY year, month)
, 1) AS growth_pct

Step 3: Handle the first month

The first month naturally has NULL for growth_pct because LAG() returns NULL when there is no previous row. No special handling is needed.