SQL Analyzing Business Metrics

Basics:

• https://gist.github.com/hofmannsven/9164408

Tutorial:

• https://www.codecademy.com/learn/sql-analyzing-business-metrics

Notes

• Order by date: order by date(created_at) (converts timestamp to date via date())
• Group by date: group by date(created_at)

Group and count by date:

select date(ordered_at), count(1)
from orders
group by date(ordered_at)
order by date(ordered_at);

Get date-related date (amount_paid) from another table for kale-smoothie only:

select date(ordered_at), round(sum(amount_paid), 2)
from orders join
order_items on
orders.id = order_items.order_id
where name = 'kale-smoothie'
group by 1
order by 1;

Get the percent of revenue for each item; Subquery to calculate the total revenue of each item:

select name, round(sum(amount_paid) /
  (select sum(amount_paid) from order_items) * 100.0, 2) as percent
from order_items
group by 1
order by 2 desc;

Group categories as category and calculate percent of amount_paid for each category:

select
  case name
    when 'kale-smoothie'    then 'smoothie'
    when 'banana-smoothie'  then 'smoothie'
    when 'orange-juice'     then 'drink'
    when 'soda'             then 'drink'
    when 'blt'              then 'sandwich'
    when 'grilled-cheese'   then 'sandwich'
    when 'tikka-masala'     then 'dinner'
    when 'chicken-parm'     then 'dinner'
    else 'other'
  end as category, round(1.0 * sum(amount_paid) /
    (select sum(amount_paid) from order_items) * 100, 2) as percent
from order_items
group by 1
order by 2 desc;

Calculate daily Revenue:

select
  date(created_at),
  round(sum(price), 2)
from purchases
group by 1
order by 1;

Daily revenue with platform; multiple group/order:

select
  date(created_at),
  platform,
  count(distinct user_id) as dau
from gameplays
group by 1, 2
order by 1, 2;

Common Table Expressions (CTEs), also known as with clauses:

with daily_revenue as (
  select
    date(created_at) as dt,
    round(sum(price), 2) as rev
  from purchases
  where refunded_at is null
  group by 1
)
select * from daily_revenue order by dt;

Self-join joins every row to every other row. This makes it possible to compare values from two different rows in the new result set.

Calculate total users/retained users using left join:

select
  date(g1.created_at) as dt,
  count(distinct g1.user_id) as total_users,
  count(distinct g2.user_id) as retained_users
from gameplays as g1
  left join gameplays as g2 on
    g1.user_id = g2.user_id
    and date(g1.created_at) = date(datetime(g2.created_at, '-1 day'))
group by 1
order by 1
limit 100;