Analyze Mobile Promo Orders with SQL Query and Metrics

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.

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.

Table users

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
sign_up_date	DATE	NOT NULL
gender	VARCHAR(1)	NOT NULL

Sample data:

country	gender	sign_up_date	user_id
US	F	2023-06-10	101
CA	M	2023-07-04	102
US	F	2023-05-12	103

Table orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES users(user_id)
order_amount	DECIMAL(10,2)	NOT NULL
order_date	DATE	NOT NULL
is_mobile	BOOLEAN	NOT NULL
is_promo	BOOLEAN	NOT NULL

Sample data:

is_mobile	is_promo	order_amount	order_date	order_id	user_id
true	false	55.3	2025-05-05	1	101
false	true	20	2025-05-10	2	102
true	true	80	2025-05-20	3	101

Expected output

Single-row result with the overall proportion of orders placed between '2025-05-03' and '2025-06-01' (inclusive) that are mobile, used a promo, have order_amount > 50, and are from US users, divided by all orders in that date range. Based on the sample data, only order_id = 3 meets all criteria, out of 3 total orders.

overall_proportion
0.3333

Hints

1. Filter orders to dates between '2025-05-03' and '2025-06-01'.
2. Join users to access the country field and restrict the numerator to US customers.
3. Use NULLIF in the denominator to avoid divide-by-zero.

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.

Table users

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
sign_up_date	DATE	NOT NULL
gender	VARCHAR(1)	NOT NULL

Sample data:

country	gender	sign_up_date	user_id
US	F	2023-06-10	101
CA	M	2023-07-04	102
US	F	2023-05-12	103

Table orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES users(user_id)
order_amount	DECIMAL(10,2)	NOT NULL
order_date	DATE	NOT NULL
is_mobile	BOOLEAN	NOT NULL
is_promo	BOOLEAN	NOT NULL

Sample data:

is_mobile	is_promo	order_amount	order_date	order_id	user_id
true	false	55.3	2025-05-05	1	101
false	true	20	2025-05-10	2	102
true	true	80	2025-05-20	3	101

Expected output

One row per gender showing the share of orders placed between '2025-05-03' and '2025-06-01' (inclusive) that used a promo. Based on the sample data, females (F) have 2 orders with 1 promo order, and males (M) have 1 order which is a promo order.

gender	promo_order_share
F	0.5
M	1

Hints

1. Filter orders to dates between '2025-05-03' and '2025-06-01'.
2. Join users to access gender.
3. Compute share as promo count divided by total orders per 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.

Table users

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
sign_up_date	DATE	NOT NULL
gender	VARCHAR(1)	NOT NULL

Sample data:

country	gender	sign_up_date	user_id
US	F	2023-06-10	101
CA	M	2023-07-04	102
US	F	2023-05-12	103

Table orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES users(user_id)
order_amount	DECIMAL(10,2)	NOT NULL
order_date	DATE	NOT NULL
is_mobile	BOOLEAN	NOT NULL
is_promo	BOOLEAN	NOT NULL

Sample data:

is_mobile	is_promo	order_amount	order_date	order_id	user_id
true	false	55.3	2025-05-05	1	101
false	true	20	2025-05-10	2	102
true	true	80	2025-05-20	3	101

Expected output

One row per country with the average order value for orders placed between '2025-05-03' and '2025-06-01' (inclusive). Based on the sample data, US has two orders (55.30 and 80.00) and CA has one order (20.00).

avg_order_value	country
20	CA
67.65	US

Hints

1. Filter orders to dates between '2025-05-03' and '2025-06-01'.
2. Join users to access country.
3. Use AVG(order_amount) grouped by country.