Katkat
Back

Explain Joins and Write Coupon SQL

eBayData Analyst · HR Screen
MediumData AnalystHR ScreenData Manipulation (SQL/Python)

Last updated

This problem tests your ability to perform multi-table joins, handle temporal data, and apply conditional aggregation to compute business KPIs like conversion rates and total transaction value.

Problem Statement

You are working for an e-commerce platform and need to analyze the performance of a recent 'SUMMER20' coupon campaign. You have two tables: coupons_usage and transactions.

Tables

coupons_usage

  • user_id (INT): Unique user identifier
  • coupon_code (VARCHAR)
  • applied_at (TIMESTAMP): Time the coupon was applied (UTC)

transactions

  • transaction_id (INT)
  • user_id (INT)
  • amount (FLOAT): Transaction value
  • transacted_at (TIMESTAMP): Time of transaction (UTC)

Task

Write a SQL query to generate a report for users who applied the 'SUMMER20' coupon in August 2023. For these users, calculate:

  1. The total number of unique users who applied the coupon.
  2. The number of those users who successfully completed at least one transaction after applying the coupon.
  3. The total Gross Merchandise Bought (GMB) by these users within 48 hours after they applied the coupon.

Constraints

  • Consider a user to have 'converted' if they completed a transaction at any time after their coupon application.
  • Only count transactions that occurred strictly after applied_at.

Example

Input:

  • coupons_usage: User 1 applied 'SUMMER20' at '2023-08-01 10:00:00'
  • transactions: User 1 completed a transaction of $50 at '2023-08-02 09:00:00'

Output:

  • total_coupon_users: 1
  • converted_users: 1
  • total_gmb_48h: 50.0

Edge Cases

  • Users who apply the coupon but never transact.
  • Users who transact multiple times within the 48-hour window.
  • Transactions occurring before the coupon was applied.

Hints

Hint 1

Use a LEFT JOIN to keep all users who applied the coupon, even if they have no corresponding transactions.

Hint 2

Use a CASE statement or filter inside an aggregate function to count only those transactions that fall within the 48-hour window (use `INTERVAL '48 hours'` or date arithmetic).

Hint 3

Ensure your grouping logic correctly counts distinct `user_id`s to avoid inflating the number of users due to multiple transactions.

Follow-up questions

  • How would your query change if you needed to calculate the conversion rate by user region, assuming a third table `user_profiles` exists?
  • Explain the difference in performance between using a LEFT JOIN versus a correlated subquery in the WHERE clause for this specific task.
  • If the timestamp column included timezone offsets, what functions would you use to normalize the data to UTC before performing the join?