SQL Event Log Analytics

What's being tested

These prompts test event-log analytics: turning raw user/action tables into product metrics with correct joins, time windows, deduplication, and aggregation. Interviewers are probing whether you can write SQL that matches metric definitions precisely, especially for `DAU`, engagement rates, call duration, survey quality, and revenue by geography.

Patterns & templates

• Grain first: identify one row’s meaning before coding; aggregate to user-day, call-day, impression-day, or country-day before final metrics.

• Time-window filtering: use WHERE event_ts >= start AND event_ts < end; avoid inclusive end dates that double-count midnight events.

• Safe distinct metrics: compute COUNT(DISTINCT user_id) for users and COUNT(*) for events; never substitute one for the other.

• Join discipline: use LEFT JOIN when preserving denominators like impressions, INNER JOIN only when matched actions define the population.

• Conditional aggregation: use SUM(CASE WHEN condition THEN 1 ELSE 0 END) or COUNT_IF for clicks, responses, qualified surveys, or completed calls.

• Ratio safety: write numerator * 1.0 / NULLIF(denominator, 0); explicitly decide whether missing ratios return NULL, 0, or are filtered.

• Deduplication template: use ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY event_ts DESC) when latest valid event or one response per user is required.

Common pitfalls

Pitfall: Joining two event tables before aggregating can multiply rows, inflating clicks, responses, revenue, or duration.

Pitfall: Using the caller’s country only may miss receiver-side usage; clarify whether metrics are per initiator, participant, or call.

Pitfall: Grouping by local date when the metric requires UTC attribution changes daily trends and geography comparisons.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.