Debug a Hive Query for DAU

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).

Compute Daily Active Users (DAU) from Partitioned Events Table

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.

Table users

column	type	constraints
user_id	BIGINT	PRIMARY KEY
created_at	TIMESTAMP

Sample data:

created_at	user_id
2025-08-10 10:00:00	1
2025-08-15 08:00:00	2
2025-08-14 09:00:00	3
2025-08-16 11:00:00	4
2025-08-01 12:00:00	5

Table events

column	type	constraints
user_id	BIGINT	NOT NULL
event_time	TIMESTAMP	NOT NULL
event_name	VARCHAR(50)	NOT NULL
event_date	VARCHAR(10)	NOT NULL

Sample data:

event_date	event_name	event_time	user_id
2025-08-15	page_view	2025-08-15 09:00:00	1
2025-08-15	purchase	2025-08-15 10:00:00	1
2025-08-15	page_view	2025-08-15 11:00:00	2
2025-08-14	page_view	2025-08-14 20:00:00	3
2025-08-15	logout	2025-08-15 01:00:00	3
2025-08-15	page_view	2025-08-15 01:05:00	3
2025-08-16	page_view	2025-08-16 09:00:00	4

Expected output

A single-row result giving the Daily Active Users for 2025-08-15, counting each user at most once if they have at least one qualifying event (event_name IN ('page_view', 'purchase')) on that date. The sample data has three such users: user_ids 1, 2, and 3.

activity_date	dau
2025-08-15	3

Hints

1. You do not need to join to the users table if you only care about counting users who generated events.
2. Avoid applying DATE() to event_time; instead, filter directly on the partition column event_date for '2025-08-15'.