SQL for Activity and User Metrics

HardSQL · PostgreSQL

Problem

ZoomInfo Technologies wants a daily view of activity volume in ZoomInfo Engage. Write a PostgreSQL query that reports activity and distinct-user counts by reportable activity type and calendar date.

Requirements

Use the reporting range from 2025-01-01 through 2025-01-04, inclusive.
Include every reportable activity type on every date, including combinations with no activities.
Return the number of activities and distinct users who performed those activities. Activities without an associated user count toward activity volume but not the user count.
Add the previous day's activity and user counts for the same activity type using a window function.
Calculate day-over-day changes for both measures, preserving NULL for the first date because no prior date exists.
Order results by activity type and activity date.

Schema

users

Column	Type	Description
user_idPK	INT	Unique ZoomInfo Engage user identifier
full_name	VARCHAR(100)	User's display name

activity_types

Column	Type	Description
activity_type_idPK	INT	Unique activity type identifier
activity_type	VARCHAR(80)	Name of the activity type
is_reportable	BOOLEAN	Indicates whether the type appears in reporting

activities

Column	Type	Description
activity_idPK	INT	Unique activity identifier
activity_type_id	INT	Referenced activity type
user_id	INT	User who performed the activity
activity_timestamp	TIMESTAMP	Timestamp when the activity occurred

Tablesusersactivity_typesactivities