Aggregate Completed Q1 Orders by Segment

The interviewer gives you a CSV and asks you to answer a business question using whatever tools you want; how do you approach it from start to finish?

For grading, assume the CSV has been loaded into csv_sales and customer reference data is available in customer_segments. Return completed, non-null sales from Q1 2025, grouped by customer segment, with unknown customers labeled Unclassified. Include only segments with at least two qualifying sales.

Output

1. One row per qualifying segment with segment, completed_orders, net_sales, and avg_order_value
2. Net sales equal amount minus discount, treating a missing discount as zero
3. Order by net_sales descending, then segment ascending

Output details

• Treat Q1 as order_date >= DATE '2025-01-01' and order_date < DATE '2025-04-01'.
• Round avg_order_value to 2 decimal places.
• Exclude rows where amount is NULL.
• Label rows whose matched segment is NULL as Unclassified.
• Count every qualifying sales row without deduplicating order_id values.