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.
