CTE Paid Totals

-- Drill 19 — CTE لتجميع الطلبات المدفوعة — SQLite 3.x+
CREATE TABLE orders (
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  total_halalas INTEGER NOT NULL,
  cancelled_at TEXT
);

INSERT INTO orders VALUES
  (1, 'paid', 5000, NULL),
  (1, 'paid', 7000, NULL),
  (1, 'paid', 9000, 'cancelled'),
  (2, 'draft', 3000, NULL);

WITH valid_paid_orders AS (
  SELECT customer_id, total_halalas
  FROM orders
  WHERE status = 'paid'
    AND cancelled_at IS NULL
)
SELECT
  customer_id,
  SUM(total_halalas) AS total_paid_halalas
FROM valid_paid_orders
GROUP BY customer_id;