Given a storyline-based business scenario, write 4 SQL queries (joins, aggregations, filtering) and discuss query optimization.
Asked in the R3 SQL stage. Final round; the recruiter had said to prepare basic system design, database design, SQL, and web architecture.
Output
- Query 1:
region, completed_orders, and completed_revenue, including regions without completed orders, ordered by revenue descending.
- Query 2:
customer_id, customer_name, and total_spent for customers spending over 100, ordered by spending descending.
- Query 3:
product_category and category_revenue for completed order items, ordered by revenue descending.
- Query 4: one row per customer with
customer_id, customer_name, latest_order_id, and latest_order_date, ordered by customer ID.
Output details
- Use
Unknown for NULL regions and 0.00 for regions without completed orders in Query 1.
- Order Query 1 by
completed_revenue descending, then region ascending.
- Compute Query 2 spending from completed orders, round
total_spent to two decimal places, and break ties by customer_id ascending.
- Round Query 3
category_revenue to two decimal places, break ties by product_category ascending, and place NULL categories last.
- Use only completed orders for Query 4, choosing the greatest
latest_order_date and then greatest latest_order_id; return NULL values when none exist.
- Order Query 4 by
customer_id ascending.