Codecademy SQL: Analyzing Business Metrics Exercise 13 of 14

--first answer

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

--second answer

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;

--third answer

select
  date(g1.created_at) as dt,
  round(100 * count(distinct g2.user_id) /
    count(distinct g1.user_id)) as retention
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;

Codecademy SQL: Analyzing Business Metrics Exercise 12 of 14

--first answer

select
  date(created_at) as dt,
  user_id
from gameplays as g1
order by dt
limit 100;

--second answer

select
  date(g1.created_at) as dt,
  g1.user_id
from gameplays as g1
  join gameplays as g2 on
    g1.user_id = g2.user_id
order by 1
limit 100;

Codecademy SQL: Analyzing Business Metrics Exercise 9 of 14

--first answer

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
),
daily_players as (
  select
    date(created_at) as dt,
    count(distinct user_id) as players
  from gameplays
  group by 1
)
select * from daily_players order by dt;

--second answer

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
),
daily_players as (
  select
    date(created_at) as dt,
    count(distinct user_id) as players
  from gameplays
  group by 1
)
select
  daily_revenue.dt,
  daily_revenue.rev / daily_players.players
from daily_revenue
  join daily_players using (dt);

Codecademy SQL: Analyzing Business Metrics Exercise 8 of 14

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;

Codecademy SQL: Analyzing Business Metrics Exercise 6 of 14

select
  date(created_at),
  round(sum(price) / count(distinct user_id), 2) as arppu
from purchases
where refunded_at is null
group by 1
order by 1;

Codecademy SQL: Analyzing Business Metrics Exercise 5 of 14

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

Codecademy SQL: Analyzing Business Metrics Exercise 4 of 14

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

Codecademy SQL: Analyzing Business Metrics Exercise 3 of 14

select
  date(created_at),
  round(sum(price), 2) as daily_rev
from purchases
where refunded_at is not null
group by 1
order by 1;

Codecademy SQL: Analyzing Business Metrics Exercise 2 of 14

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

Codecademy SQL: Analyzing Business Metrics Exercise 1 of 14

--first answer

select *
from purchases
order by id
limit 10;

--second answer

select *
from gameplays
order by id
limit 10;

Codecademy SQL SpeedySpoon Exercise 11

select name, count(DISTINCT order_id)
from order_items
group by 1
order by 1;

-- number of people making the orders

select name, round(1.0 * count(DISTINCT order_id) /
  count(DISTINCT delivered_to), 2) as reorder_rate
from order_items
  join orders on
    orders.id = order_items.order_id
group by 1
order by 2 desc;

Codecademy SQL SpeedySpoon Exercise 9

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
from order_items
order by id
limit 100;

-- percentages

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 pct
from order_items
group by 1
order by 2 desc;

Codecademy SQL SpeedySpoon Exercise 7

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

Codecademy SQL SpeedySpoon Exercise 6

select name, round(sum(amount_paid), 2)
from order_items
group by name
order by 2 desc;

Codecademy SQL SpeedySpoon Exercise 4

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

--for kale-smoothie

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;

Codecademy SQL SpeedySpoon Exercise 3

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

Codecademy SQL SpeedySpoon Exercise 2

select date(ordered_at)
from orders
order by 1
limit 100;

Codecademy SQL SpeedySpoon Exercise 1

select *
from orders
order by id
limit 100;

select *
from order_items
order by id
limit 100;

Codecademy SQL Replace enriched_flour with flour

SELECT REPLACE(ingredients,'enriched_',' ') as item_ingredients
FROM baked_goods;

Codecademy SQL Combine the first_name and last_name columns from the bakeries table

SELECT first_name || ' ' || last_name as full_name
FROM bakeries;