PostgreSQL queries for various present time series views The last query is an example of using window function over whole range (w/o PARTITION BY) with ORDER BY for creating a running total.

queries4juan.sql

-- // How many events were created from date A to date B (possibly presenting data on a daily, weekly and monthly basis)
-- Replace 'week' with 'day' or 'month'
-- date_trunc takes: minute hour day week month quarter year
-- NB! TIMESTAMP '2013-09-16' == '2013-09-16 00:00:00' so BETWEEN includes only tickets sold until that date.
SELECT date_trunc('week', created_at) AS dt, count(*) AS total_events
FROM events 
WHERE created_at BETWEEN TIMESTAMP '2013-06-16' AND TIMESTAMP '2013-09-16'
GROUP BY date_trunc('week', created_at)

-- // How many paid tickets were sold form date A to date B (presenting graphs on a day to day basis, weekly and monthly)
-- assuming that created_at is date when the tickets got sold
SELECT date_trunc('week', created_at) AS dt, sum(tickets_total) AS sold_tickets
FROM bookings 
WHERE created_at BETWEEN TIMESTAMP '2013-06-16' AND TIMESTAMP '2013-09-16'
  AND state='complete'
GROUP BY date_trunc('week', created_at)

-- // How many paid tickets were sold from date A to date B, on a weekly/monthly basis for events that belong to the 'Music' category.
SELECT date_trunc('week', b.created_at) AS dt, sum(tickets_total) AS sold_tickets
FROM bookings AS b JOIN events AS e ON event_id=e.id
WHERE b.created_at BETWEEN TIMESTAMP '2013-06-16' AND TIMESTAMP '2013-09-16'
  AND b.state='complete' AND e.event_type = 'Musical'
GROUP BY date_trunc('week', b.created_at)


-- // How the accumulated number of sold, paid and free tickets has behaved through time? (a running total) so, for example week 1 we had 100 events week 2 102, week 3 110 and so on from date A to date B.

WITH pp AS ( -- per period:

  SELECT
    date_trunc('week', b.created_at) AS dt,
    sum(b.tickets_total) AS total_sold,
    count( NULLIF(price, 0.00::money) ) AS total_free -- NB! you might need to delete the casting to money type.
  FROM tickets AS t JOIN bookings AS b ON t.booking_id = b.id
  WHERE b.created_at BETWEEN TIMESTAMP '2013-06-16' AND TIMESTAMP '2013-09-16'
    AND state='complete'
  GROUP BY date_trunc('week', b.created_at)
  -- ORDER BY 1

)
SELECT dt,
  sum(total_sold) OVER w AS total_sold,
  sum(total_free) OVER w AS total_free,
  sum(total_sold-total_free) OVER w AS total_paid
FROM pp
WINDOW w AS ( ORDER BY dt);


--  // Schema with test data:
/*
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS tickets;

CREATE TABLE events (
  id serial PRIMARY KEY,
  name text,
  event_type text,
  start_date timestamp without time zone,
  end_date timestamp without time zone,
  created_at timestamp without time zone NOT NULL
);

CREATE TABLE bookings (
  id serial PRIMARY KEY,
  state text DEFAULT 'cart',
  tickets_total int,
  total money,
  event_id int,
  created_at timestamp without time zone NOT NULL,
  payment_method_type text
);

CREATE TABLE tickets (
  id serial PRIMARY KEY,
  number text,
  price money,
  booking_id int,
  created_at timestamp without time zone NOT NULL
);


WITH et(et) AS ( VALUES ('Otro'), ('Deportivo / De resistencia'), ('Comida y Bebida'), ('Recaudación de fondos'), ('Congreso / Conferencia'), ('Musical'), ('Comunidad / Networking'), ('Taller / Curso')),
rd(ts) AS (SELECT now()-'1 day'::interval * ROUND(30+RANDOM()*200) AS ts FROM generate_series(1,100))
INSERT INTO events (name,event_type, start_date, end_date, created_at)
SELECT
  md5(user||current_timestamp||random()) AS name, et, ts, ts+'1 day'::interval, ts-'10 day'::interval
FROM et, rd;

WITH pmt(pmt) AS (VALUES ('Gateway::Oxxo'),('PaymentMethod::Deposit'),('Gateway::Banwire'),('PaymentMethod::Paypal')),
  st(st) AS (VALUES ('pending'),('complete'),('payment_method_selection'),('pay'),('ticket_selection'),('rejected'),('canceled'))
INSERT INTO bookings (state,tickets_total,event_id,created_at,payment_method_type)
SELECT st,(random()*10+1)::int,e.id,e.created_at+('1 hour'::interval*(random()*100)),pmt
FROM events AS e, pmt, st, generate_series(1,10);

WITH rb(id) AS (
  SELECT (random()*(SELECT max(id)+1 FROM bookings)+(SELECT min(id) FROM bookings))::int
  FROM generate_series(1,3*(SELECT count(*) FROM bookings)) AS gs(id))
INSERT INTO tickets (number,price,booking_id,created_at)
SELECT md5(user||current_timestamp||random()),(random()*300)::int,rb.id,b.created_at+('1 hour'::interval*(random()*100))
FROM rb JOIN bookings AS b USING(id);

WITH summary AS (
  SELECT booking_id, sum(price) AS t, count(*) AS c FROM tickets GROUP BY booking_id)
UPDATE bookings 
SET total=t, tickets_total=c
FROM summary AS s WHERE s.booking_id = bookings.id
*/