Debug a Hive Query for DAU
Company: TikTok
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given two Hive tables: users(user_id BIGINT, created_at TIMESTAMP) and events(user_id BIGINT, event_time TIMESTAMP, event_name STRING) PARTITIONED BY (event_date STRING in 'YYYY-MM-DD'). A teammate wrote the query: "SELECT u.user_id, COUNT(DISTINCT e.user_id) AS dau FROM users u LEFT JOIN events e ON u.user_id = e.user_id WHERE DATE(e.event_time) = '2025-08-15' GROUP BY u.user_id;" This is intended to return the site-wide Daily Active Users for 2025-08-15. Identify at least three bugs or inefficiencies (e.g., join semantics, grouping grain, partition pruning, time handling), rewrite a correct and efficient Hive-compatible query that outputs a single DAU number for that date (assuming relevant event_names define activity), and explain how you would validate correctness and performance (test cases, edge cases, and use of partitions/statistics).
Overview: This question evaluates proficiency in Hive/SQL query formulation, data partitioning and pruning, join and aggregation semantics, timestamp handling, and performance tuning for large-scale analytics.
Read the full TikTok Data Engineer interview experience this question came from
You are given two Hive tables:
- users(user_id BIGINT, created_at TIMESTAMP)
- events(user_id BIGINT, event_time TIMESTAMP, event_name STRING) PARTITIONED BY (event_date STRING in 'YYYY-MM-DD')
A teammate wrote the following query:
"SELECT u.user_id, COUNT(DISTINCT e.user_id) AS dau
FROM users u
LEFT JOIN events e
ON u.user_id = e.user_id
WHERE DATE(e.event_time) = '2025-08-15'
GROUP BY u.user_id;"
This query is intended to return the site-wide Daily Active Users (DAU) for 2025-08-15, but instead it produces one row per user and suffers from several bugs/inefficiencies (join semantics, grouping grain, partition pruning, time handling).
Using the schema and sample data below, write a correct and efficient Hive-compatible SQL query that:
- Computes the site-wide DAU for the date '2025-08-15'.
- Counts a user as active if they have at least one qualifying event in events on that date, where qualifying events are event_name IN ('page_view', 'purchase').
- Uses the partition column event_date for filtering, so that Hive can prune partitions instead of calling functions on event_time.
- Returns a single row with two columns: activity_date (as '2025-08-15') and dau (the count of distinct active users).
You do not need to reproduce or explain the bugs in the original query; just provide the corrected query.
Tables
users(user_id BIGINT, created_at TIMESTAMP)
events(user_id BIGINT, event_time TIMESTAMP, event_name VARCHAR(50), event_date VARCHAR(10))
Hints
- You do not need to join to the users table if you only care about counting users who generated events.
- Avoid applying DATE() to event_time; instead, filter directly on the partition column event_date for '2025-08-15'.