hourly_events

Usage distribution by hour of the day. Lets you spot traffic peaks, find the best window for campaigns and the safest window for maintenance.

• Grain: (id_tenant, date, hour)
• ETL strategy: merge
• PK: (id_tenant, date, hour)

When to use it

• Heatmap "hour × day-of-week".
• Defining maintenance windows (which hour has the lowest activity?).
• Optimizing email / push send times (which hour drives the best response?).

Columns

Column	Type	Description
id_tenant	BIGINT	FK → tenants.id. Part of PK.
date	DATE	Day. Part of PK.
hour	SMALLINT	Hour of the day (0..23). Part of PK.
total	INTEGER	Events recorded in that hour-day.

Usage pattern

-- Heatmap: average hour × day-of-week (last 90 days)
SELECT extract(dow  from date)::int AS day_of_week,  -- 0=Sunday
       hour,
       round(avg(total)) AS avg_events
FROM metrics.hourly_events
WHERE date >= current_date - interval '90 days'
GROUP BY 1, 2
ORDER BY 1, 2;

-- Lowest-activity hour (good for maintenance)
SELECT hour, sum(total) AS volume
FROM metrics.hourly_events
WHERE date >= current_date - interval '30 days'
GROUP BY hour
ORDER BY volume
LIMIT 3;