Scenario

You work for a telecommunications company and have been given a table of call records.

Task

Write a Snowflake SQL query that:

1. Uses {{ ref("calls") }} as your source table
2. Counts the number of distinct customers (cust_id) per date as num_customers
3. Sums the duration column per date as total_duration
4. Groups results by date

Schema

calls

Example

calls:

Expected Output:

Note: On 2023-06-01, customer 101 made two calls but is counted only once for num_customers. All call durations on that date are still summed for total_duration.

SELECT
    date,
    COUNT(DISTINCT cust_id) AS num_customers,
    SUM(duration) AS total_duration
FROM {{ ref("calls") }}
GROUP BY date

Explanation

Step 1: Counting distinct customers

COUNT(DISTINCT cust_id) AS num_customers

COUNT(DISTINCT cust_id) counts each unique customer once per group, even if that customer made multiple calls on the same date. Without DISTINCT, a customer who called three times would be counted three times.

Step 2: Summing duration

SUM(duration) AS total_duration

SUM(duration) adds every call's duration within the group. Unlike the customer count, you want to include all calls here (a customer with three calls contributes all three durations to the total).

Step 3: Grouping by date

GROUP BY date

GROUP BY date collapses all rows sharing the same date into a single output row. Every non-aggregated column in SELECT must appear in the GROUP BY clause.

Step 4: Alternative with CTE

You can wrap the aggregation in a CTE for readability:

WITH daily_summary AS (
    SELECT
        date,
        COUNT(DISTINCT cust_id) AS num_customers,
        SUM(duration) AS total_duration
    FROM {{ ref("calls") }}
    GROUP BY date
)
SELECT * FROM daily_summary

Both approaches produce identical results. CTEs become more useful when chaining multiple transformation steps.