Production Report

-- Drill 25 — تقرير إنتاجي متكامل بـ CTE — SQLite 3.x+
CREATE TABLE orders (
  created_date TEXT NOT NULL,
  status TEXT NOT NULL,
  total_halalas INTEGER NOT NULL,
  cancelled_at TEXT
);

INSERT INTO orders VALUES
  ('2026-04-30', 'paid', 5000, NULL),
  ('2026-04-30', 'paid', 7000, NULL),
  ('2026-04-30', 'paid', 9000, 'cancelled'),
  ('2026-05-01', 'draft', 3000, NULL);

WITH valid_paid_orders AS (
  SELECT created_date, total_halalas
  FROM orders
  WHERE status = 'paid'
    AND cancelled_at IS NULL
)
SELECT
  created_date,
  COUNT(*) AS paid_order_count,
  SUM(total_halalas) AS paid_total_halalas
FROM valid_paid_orders
GROUP BY created_date
ORDER BY created_date;