Agent View

-- Synthetic reference only. Run in a fresh PostgreSQL test session. -- TEMP objects disappear with the session; never load this into production. CREATE TEMP TABLE dashboard_params ( start_date date, end_date date, refund_before date, tenant_id text, currency text ); INSERT INTO dashboard_params VALUES ('2026-09-01','2026-09-05','2026-09-05','A','USD'); CREATE TEMP TABLE dashboard_orders ( id integer PRIMARY KEY, tenant_id text, ordered_on date, status text, currency text, region text, gross_cents integer ); INSERT INTO dashboard_orders VALUES (101,'A','2026-09-01','paid','USD','North',12000), (102,'A','2026-09-02','paid','USD','North',8000), (103,'A','2026-09-02','paid','USD','South',6000), (104,'A','2026-09-03','cancelled','USD','South',9000), (105,'A','2026-09-03','paid','USD','North',4000), (106,'A','2026-10-01','paid','USD','North',50000), (107,'A','2026-09-01','paid','EUR','North',7000), (108,'B','2026-09-01','paid','USD','North',90000); CREATE TEMP TABLE dashboard_refunds ( id integer PRIMARY KEY, order_id integer, refunded_on date, amount_cents integer ); INSERT INTO dashboard_refunds VALUES (1,101,'2026-09-02',1000), (2,101,'2026-09-03',500), (3,103,'2026-09-03',600), (4,101,'2026-10-01',2000); CREATE TEMP VIEW dashboard_base AS WITH refunds_per_order AS ( SELECT r.order_id, SUM(r.amount_cents) AS refund_cents FROM dashboard_refunds r CROSS JOIN dashboard_params p WHERE r.refunded_on < p.refund_before GROUP BY r.order_id ) SELECT o.id, o.ordered_on, o.region, o.gross_cents - COALESCE(r.refund_cents,0) AS net_cents FROM dashboard_orders o CROSS JOIN dashboard_params p LEFT JOIN refunds_per_order r ON r.order_id = o.id WHERE o.tenant_id = p.tenant_id AND o.currency = p.currency AND o.status = 'paid' AND o.ordered_on >= p.start_date AND o.ordered_on < p.end_date; -- Q1: KPI cards: net_cents=27900, orders=4, average_order_cents=6975.00 SELECT COALESCE(SUM(net_cents),0) AS net_cents, COUNT(*) AS orders, ROUND(SUM(net_cents)::numeric / NULLIF(COUNT(*),0),2) AS average_order_cents FROM dashboard_base; -- Q2: Daily chart: Sep01=10500, Sep02=13400, Sep03=4000, Sep04=0 WITH calendar AS ( SELECT generate_series(p.start_date::timestamp, (p.end_date-1)::timestamp, interval '1 day')::date AS day FROM dashboard_params p ) SELECT c.day, COALESCE(SUM(b.net_cents),0) AS net_cents, COUNT(b.id) AS orders FROM calendar c LEFT JOIN dashboard_base b ON b.ordered_on = c.day GROUP BY c.day ORDER BY c.day; -- Q3: Regional table: North=22500/3 orders; South=5400/1 order SELECT region, SUM(net_cents) AS net_cents, COUNT(*) AS orders, ROUND(SUM(net_cents)::numeric / COUNT(*),2) AS average_order_cents FROM dashboard_base GROUP BY region ORDER BY region;