Call Classifications

Query

--canonical classifications for lengths of calls

SELECT
    TO_TIMESTAMP(call_date)::date AS CALL_DATE,
    sum(CASE WHEN TALK_TIME_SECONDS < 20 THEN 1 else 0 end) as quick_drops,
    sum(CASE WHEN TALK_TIME_SECONDS >= 20 AND TALK_TIME_SECONDS < 45 THEN 1 else 0 end) as micro_interactions,
    sum(CASE WHEN TALK_TIME_SECONDS >= 45  AND TALK_TIME_SECONDS < 120 THEN 1 else 0 end) as real_calls_brief,
    sum(CASE WHEN TALK_TIME_SECONDS >= 120 AND TALK_TIME_SECONDS < 300 THEN 1 else 0 end) as quality_calls,
    sum(CASE WHEN TALK_TIME_SECONDS >= 300 THEN 1 else 0 end) as deep_calls,
    COUNT(*) AS all_calls,
    sum(talk_time_seconds / 60) as total_talk_minutes
FROM ANALYTICS_DB.ANALYTICS.CALL_LOGS
WHERE TALK_TIME_SECONDS IS NOT NULL
GROUP BY ALL
ORDER BY MIN(TALK_TIME_SECONDS);

Overview

Defines the canonical duration tier classifications for Tin Can calls. Aggregates completed calls from ANALYTICS_DB.ANALYTICS.CALL_LOGS by date, bucketing each call into one of five duration tiers based on TALK_TIME_SECONDS. Calls with no recorded talk time are excluded.

These tier boundaries are the shared reference for call length analysis across reports — if the classifications need to change, this is the place to change them.

---

Duration Tiers

Column	Tier Name	Duration Range
quick_drops	Quick Drop	< 20 seconds
micro_interactions	Micro Interaction	20–44 seconds
real_calls_brief	Real Call (Brief)	45–119 seconds
quality_calls	Quality Call	2–5 minutes
deep_calls	Deep Call	5+ minutes

---

Output Columns

Column	Description
call_date	Date of the call
quick_drops	Calls under 20 seconds
micro_interactions	Calls between 20 and 44 seconds
real_calls_brief	Calls between 45 seconds and 2 minutes
quality_calls	Calls between 2 and 5 minutes
deep_calls	Calls 5 minutes or longer
all_calls	Total completed calls (all tiers combined)
total_talk_minutes	Sum of all talk time in minutes

---

Notes

• Only calls with a non-null TALK_TIME_SECONDS are included. Calls that were placed but not answered (or where duration was not recorded) are excluded.
• Tier boundaries are inclusive on the lower bound and exclusive on the upper bound.
• This query has no dependency on the date spine — it aggregates directly from CALL_LOGS and produces one row per date that has call activity. Dates with no calls will not appear in the output; downstream queries should left-join this result onto included_dates if a continuous date series is needed.