The multi-condition filter part was fine, I set up a CTE to separate the denominator (all orders) from the numerator (filtered orders) and used conditional aggregation.
Start by clarifying the schema and assumptions (e.g., date range, promo flag, mobile indicator). Write a single SQL query that filters orders based on the given conditions and computes the proportion, then join with users to add two business metrics like average order value and repeat order rate. Explain each metric's relevance to Meta's product analytics.
Pro tip: Use a CTE to isolate the filtered orders and calculate the proportion, then join with users for additional metrics. This keeps the query modular and easy to explain, showing you can write maintainable SQL.
Confirm the definitions of 'mobile', 'promo', 'US customer', and 'last 30 days' relative to the current date. State any assumptions about the schema (e.g., column names, data types).
Use a CTE to filter orders meeting all conditions, then calculate the proportion as the count of qualifying orders divided by the total number of orders. Ensure the date filter uses a dynamic date function like CURRENT_DATE - INTERVAL '30 days'.
Join the filtered orders with the users table on user_id. Define at least two business metrics, such as average order value (AOV) and repeat order rate, and write SQL for each.
For each additional metric, provide a one-sentence justification explaining how it provides business value or insight for Meta.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.