عرض عملي: بناء تقرير المبيعات الشهري بـ SQL

خطوة بخطوة

1. Show the Java version that loads all orders and aggregates in memory; note its row count and time.
2. Write a CTE monthly that groups paid orders by date_trunc('month', created_at) and category.
3. Add rank() OVER (PARTITION BY month ORDER BY revenue DESC) and keep the top 3.
4. Add lag(revenue) for month-over-month change and FILTER for refunded counts.
5. Planned mistake: put the rank() condition in WHERE; PostgreSQL rejects it. Fix: wrap in a CTE and filter outside.
6. Compare time and data transferred with the Java version.

Prepares MID-011-LAB1.

اختبر فهمك