clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335846 fiddles created (27456 in the last week).

CREATE TABLE orders ( id SERIAL, created_at timestamp NOT NULL, PRIMARY KEY (id) ); INSERT INTO "orders" ("created_at") VALUES (now() - interval '1 hours'), (now() - interval '6 hours'), (now() - interval '12 hours'), (now() - interval '2 days'), (now() - interval '4 days'), (now() - interval '6 days'), (now() - interval '9 days');
7 rows affected
 hidden batch(es)


--= Count orders grouping by date SELECT created_at::date, COUNT(*) num_orders FROM orders WHERE created_at < (now() - interval '1 days') AND created_at >= (now() - interval '8 days') GROUP BY created_at::date;
created_at num_orders
2018-11-24 1
2018-11-26 1
2018-11-28 1
 hidden batch(es)


--= Count orders grouping by date WITH cn AS ( SELECT created_at::date, COUNT(*) num_orders FROM orders WHERE created_at < (now() - interval '1 days') AND created_at >= (now() - interval '8 days') GROUP BY created_at::date ) SELECT to_char(AVG(num_orders), '9.99') AS "AvgOrderVelocity", to_char(SUM(num_orders) / 7::decimal(18,2), '0.99') AS "AvgLast7Days", (SELECT COUNT(*) FROM orders WHERE created_at > (now() - interval '1 days')) "Last24h" FROM cn;
AvgOrderVelocity AvgLast7Days Last24h
1.00 0.43 3
 hidden batch(es)