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.