jdwyah

select date_trunc('month', date), count(distinct user_id) from events
where event = "login" group by 1 order by 1

jdwyah

with monthly_usage as (
  select 
    who_identifier, 
    datediff(month, '1970-01-01', when_timestamp) as time_period
  from events
  where event = 'login' group by 1,2 order by 1,2),

jdwyah

lag_lead as (
  select who_identifier, time_period,
    lag(time_period,1) over (partition by who_identifier order by who_identifier, time_period),
    lead(time_period,1) over (partition by who_identifier order by who_identifier, time_period)
  from monthly_usage),

jdwyah

lag_lead_with_diffs as (
  select who_identifier, time_period, uses_outlook, lag, lead, 
    time_period-lag lag_size, 
    lead-time_period lead_size 
  from lag_lead),

jdwyah

calculated as (select time_period,
  case when lag is null then 'NEW'
     when lag_size = 1 then 'ACTIVE'
     when lag_size > 1 then 'RETURN'
  end as this_month_value,
 
  case when (lead_size > 1 OR lead_size IS NULL) then 'CHURN'
     else NULL
  end as next_month_churn,
 

jdwyah

create table events (
user_id int
date date_time
event text
)

jdwyah

SELECT
  date_trunc('day', page.timestamp)
  count(distinct page.anonymous_id),
  count(distinct conversions.user_id)
FROM pages page
  JOIN identifies ids
  LEFT JOIN conversions ON conversions.user_id = identifies.user_id
WHERE
  ids.anonymous_id = page.anonymous_id
  AND completes.user_id = ids.user_id

jdwyah

WITH monthly_usage AS (
    SELECT
      user_id,
      date_part('month', age(created_at, '1970-01-01')) +
      12 * date_part('year', age(created_at, '1970-01-01')) AS time_period
    FROM orders
    WHERE order_state = 'completed'
    GROUP BY 1, 2
    ORDER BY 1, 2)

jdwyah

CREATE TABLE events (
  event_guid           CHARACTER VARYING(128),
  tracking_id          CHARACTER VARYING(128),
  category             CHARACTER VARYING(128) encode TEXT255,
  sub_category         CHARACTER VARYING(128),
  event                CHARACTER VARYING(128) encode TEXT255,
  event_name           CHARACTER VARYING(128) encode TEXT255,  
  value_decimal        NUMERIC(38, 6),
  value_string         CHARACTER VARYING(255),
  created_at           TIMESTAMP WITHOUT TIME ZONE,

jdwyah

 WITH first_exposures AS (
      SELECT
        tracking_id,
        created_at,
        event,
        experiment_test,
        experiment_result,
        date_trunc('day', created_at) AS day
      FROM warehouse.events
      WHERE category = 'experiment'