Analyze Mobile Promo Orders with SQL Query and Metrics
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
orders
+-----------+---------+--------------+------------+-----------+----------+
| order_id | user_id | order_amount | order_date | is_mobile | is_promo |
+-----------+---------+--------------+------------+-----------+----------+
| 1 | 101 | 55.30 | 2023-08-01 | true | false |
| 2 | 102 | 20.00 | 2023-08-02 | false | true |
| 3 | 101 | 80.00 | 2023-08-05 | true | true |
+-----------+---------+--------------+------------+-----------+----------+
users
+---------+---------+--------------+--------+
| user_id | country | sign_up_date | gender |
+---------+---------+--------------+--------+
| 101 | US | 2023-06-10 | F |
| 102 | CA | 2023-07-04 | M |
| 103 | US | 2023-05-12 | F |
+---------+---------+--------------+--------+
##### Scenario
Meta e-commerce storefront wants to understand recent promotional performance on mobile orders.
##### Question
Using the orders table, write a SQL query that returns the proportion of orders that satisfy ALL of these conditions: placed via mobile, order_amount > 50, order_date within the last 30 days, used a promo, and made by a US customer. 2. Join the result with the users table and create at least two additional business-relevant metrics of your choice (e.g., promo-order share by gender, average order value by country). Provide SQL and briefly justify each metric.
##### Hints
Use CTEs, conditional aggregation, and clearly separate numerator vs. denominator; justify metric selection in one sentence each.
Overview: This question evaluates SQL data-manipulation and analytical competencies, including filtered joins, conditional aggregation, date-window filtering, and business-metric design to measure promotional performance on mobile orders.
Overall Mobile Promo Proportion
Calculate the proportion of orders placed from 2025-05-03 through 2025-06-01 inclusive that satisfy all numerator criteria: mobile order, promo used, `order_amount > 50`, and a US user. The denominator is every order in that date range, regardless of channel, promo usage, amount, or country. Return one numeric column named `overall_proportion`.
Tables
users(user_id INTEGER, country VARCHAR(2), sign_up_date DATE, gender VARCHAR(1))
orders(order_id INTEGER, user_id INTEGER, order_amount DECIMAL(10,2), order_date DATE, is_mobile BOOLEAN, is_promo BOOLEAN)
Hints
- Filter orders to dates between '2025-05-03' and '2025-06-01'.
- Join users to access the country field and restrict the numerator to US customers.
Promo Share by Gender
For orders placed from 2025-05-03 through 2025-06-01 inclusive, compute the share of orders that used a promo for each user gender. Return `gender` and `promo_order_share`, ordered by gender.
Tables
users(user_id INTEGER, country VARCHAR(2), sign_up_date DATE, gender VARCHAR(1))
orders(order_id INTEGER, user_id INTEGER, order_amount DECIMAL(10,2), order_date DATE, is_mobile BOOLEAN, is_promo BOOLEAN)
Hints
- Filter orders to dates between '2025-05-03' and '2025-06-01'.
- Join users to access gender.
Average Order Value by Country
For orders placed from 2025-05-03 through 2025-06-01 inclusive, compute average order amount by user country. Return `country` and `avg_order_value`, ordered by country.
Tables
users(user_id INTEGER, country VARCHAR(2), sign_up_date DATE, gender VARCHAR(1))
orders(order_id INTEGER, user_id INTEGER, order_amount DECIMAL(10,2), order_date DATE, is_mobile BOOLEAN, is_promo BOOLEAN)
Hints
- Filter orders to dates between '2025-05-03' and '2025-06-01'.
- Join users to access country.