tickit-sample-analytic-queries

1) Sales by Date

SELECT caldate, sum(qtysold) qty, sum(pricepaid) revenue
FROM   demodb.sales, demodb.date
WHERE  sales.dateid = date.dateid 
GROUP BY caldate
ORDER BY caldate;

2) Sales per event

SELECT eventname, total_price
FROM  (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile
       FROM (SELECT eventid, sum(pricepaid) total_price
             FROM   demodb.sales
             GROUP BY eventid)) Q, demodb.event E
       WHERE Q.eventid = E.eventid
       AND percentile = 1
ORDER BY total_price desc;

3) Total Quantity per buyer

SELECT firstname, lastname, total_quantity 
FROM   (SELECT buyerid, sum(qtysold) total_quantity
        FROM  demodb.sales
        GROUP BY buyerid
        ORDER BY total_quantity desc limit 10) Q, demodb.users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc;