Queries to remember

purchases.sqlite

SELECT purchase_id, DATE(purchase_date, '7 days')
FROM purchases;

SELECT STRFTIME('%H', purchase_date)
FROM purchases;

SELECT date, (CAST(high AS 'REAL') +
  CAST(low as 'REAL')) / 2.0 AS 'average'
FROM weather
;

SELECT ROW_NUMBER()
OVER (
  ORDER BY gross
) AS 'row_num', title, week, gross
FROM box_office;

SELECT title, week, gross, 
  SUM(gross) OVER (
    PARTITION BY title 
    ORDER BY week
  ) AS 'running_total_gross'
FROM box_office;

SELECT strftime('%H',purchase_date) 
     AS 'Hour',
   COUNT(strftime('%H',purchase_date)) 
     AS 'Purchases'
FROM purchases 
GROUP BY 1 
ORDER BY 2 desc;