Combine Different Measures Using SQL Join

Given the following 2 tables:

CREATE TABLE users (
  id integer,
  email varchar
  created_at timestamp
)

CREATE TABLE products (
  id integer,
  name varchar,
  created_at timestamp
)

CREATE TABLE reviews (
  id integer,
  user_id integer,
  product_id integer
  content text,
  created_at timestamp
)

Now, you want to write a query that shows in 1 table, how many new users, new products, new reviews are being added daily.

date_d	new_users	new_products	new_reviews
2017-12-02	571	21	49
2017-12-01	500	30	65

Solution

To perform this, we will:

• We notice since we only care about daily counts of each metric, each metric has no dependency on each other.
• Thus it's most efficient to run the counts individually, and combine them together using the date key

Our final SQL will look like:

with daily_users as (
  select
    created_at::date as date_d,
    count(1) as new_users
  from users
  group by 1
), daily_products as (
  select
    created_at::date as date_d,
    count(1) as new_users
  from users
  group by 1

), daily_reviews as (
  select
    created_at::date as date_d,
    count(1) as new_users
  from users
  group by 1
)

select
  U.date_d,
  U.new_users,
  P.new_products,
  R.new_reviews
from daily_users U, daily_products P, daily_reviews R
where U.date_d = P.date_d
  and U.date_d = R.date_d
order by 1 DESC