Debug a Hive query

You are given a prewritten Hive SQL query that produces incorrect results. Describe how you would debug it: identify logical errors (e.g., join conditions, aggregations, window functions, filters, partition predicates), fix the query, and validate correctness with test cases and data checks. Explain any performance optimizations you would make and why.

Fix and Optimize a Monthly GMV Aggregation in Hive

You are computing monthly revenue metrics for an e-commerce platform. Using the users, orders, and payments tables described below, write a single PostgreSQL query that produces one row per (calendar month, country) combination, restricted to the first quarter of 2025.

Qualifying-order rules (an order must satisfy ALL of these to be counted):

• The order's status is 'completed'.
• The order's order_date falls between 2025-01-01 and 2025-03-31 (inclusive).
• There exists at least one row in payments for that order whose payment_date is between the order's order_date and 7 days after order_date (inclusive). An order is counted once no matter how many payments it has.

Output: For each (month, country) that has at least one qualifying order, return exactly these columns:

• order_month — the order's month in YYYY-MM format (derived from order_date).
• country — the country of the order's user (from users.country).
• total_gmv — the sum of orders.total_amount over the qualifying orders in that (month, country).
• paying_users — the number of distinct users who placed at least one qualifying order in that (month, country).
• avg_order_value — total_gmv divided by the number of qualifying orders in that (month, country), rounded to 2 decimal places.
• top_customer_id — the user_id whose summed total_amount of qualifying orders is the highest within that (month, country). Break ties by choosing the smallest user_id.

Sort the result by order_month ascending, then country ascending.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
signup_date	DATE

Sample data:

country	signup_date	user_id
US	2024-12-15	1
US	2024-12-20	2
CA	2025-01-05	3
US	2025-02-01	4

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL
total_amount	DECIMAL(10,2)	NOT NULL

Sample data:

order_date	order_id	status	total_amount	user_id
2025-01-05	101	completed	100	1
2025-01-20	102	cancelled	50	1
2025-01-15	103	completed	80	2
2025-02-10	104	completed	120	2
2025-02-12	105	completed	200	3
2025-03-01	106	completed	150	4
2024-12-28	107	completed	60	1
2025-03-20	108	pending	90	3
2025-03-25	109	completed	50	2

Table payments

column	type	constraints
payment_id	INT	PRIMARY KEY, NOT NULL
order_id	INT	NOT NULL
payment_date	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
payment_method	VARCHAR(20)	NOT NULL

Sample data:

amount	order_id	payment_date	payment_id	payment_method
100	101	2025-01-06	1001	card
50	102	2025-01-21	1002	card
80	103	2025-01-16	1003	paypal
120	104	2025-02-15	1004	card
200	105	2025-02-20	1005	card
150	106	2025-03-05	1006	card
60	107	2024-12-29	1007	card
50	109	2025-04-15	1008	card

Expected output

One row per (order_month, country) for qualifying completed orders in Q1 2025; only US qualifies in the sample data, yielding three rows (Jan/Feb/Mar) with GMV, distinct paying users, rounded avg order value, and the top customer by GMV.

avg_order_value	country	order_month	paying_users	top_customer_id	total_gmv
90.00	US	2025-01	2	1	180.00
120.00	US	2025-02	1	2	120.00
150.00	US	2025-03	1	4	150.00

Hints

1. Filter to completed Q1-2025 orders first, and use an EXISTS subquery against payments (rather than a direct join) so an order with several qualifying payments is still counted only once.
2. In Postgres, add days to a DATE with order_date + 7, and format the month with TO_CHAR(order_date, 'YYYY-MM').
3. Compute per-user monthly GMV in one CTE, then use ROW_NUMBER() OVER (PARTITION BY order_month, country ORDER BY user_gmv DESC, user_id ASC) and keep row 1 to get top_customer_id.