1.advanced-postgresql.sql

WITH R AS (
  SELECT (case when strpos(R.msisdn, '372') = 1 then substr(R.msisdn, 4)  else R.msisdn end) as redshift_msisdn, R.msisdn as full_msisdn,
       R."timestamp" as redshift_timestamp
  FROM (
    SELECT *
    FROM dblink('redshift_server',$REDSHIFT$
       SELECT u.msisdn, u.timestamp from user_sessions u
        WHERE u.timestamp >= '2018-06-10Z'  
          AND u.offer_id = 853
          AND u.country_code = 'EE'
          AND u.sale
        ORDER BY u.timestamp
    $REDSHIFT$) AS t1 (msisdn text, timestamp timestamp)
   ) as R
),

T as (

  select * from (
  
      select 
          msisdn
        , sum(pins) as pins
        , bool_or(pin_ever_valid) as pin_ever_valid
        , min(first_lead_time) as first_lead_time
        , min(first_pin_creation_time) as first_pin_creation_time 
        , min(first_valid_pin_creation_time) as first_valid_pin_creation_time 
        , max(last_pin_creation_time) as last_pin_creation_time 
        , array_agg(all_pins) as all_pins
        , array_agg(round( date_part('second', pin_delta))) as pin_entry_deltas
        , json_agg(query_string_values) as query_string 
        
      from (
          select 
              msisdn
            , count(pin) as pins
            , bool_or(pin_is_valid) as pin_ever_valid
            , min(creation_time) as first_lead_time
            , min(pin_creation_time) as first_pin_creation_time 
            , min(case when pin_is_valid then pin_creation_time else null end) as first_valid_pin_creation_time 
            , max(pin_creation_time) as last_pin_creation_time 
            , string_agg(pin, ',') as all_pins
            , pin_creation_time - lag(pin_creation_time) over (partition by msisdn order by pin_creation_time) as pin_delta
            , COALESCE(json_agg(query_string) FILTER (WHERE query_string IS NOT NULL), '[]') as query_string
          from berg_summary 
          where creation_time > '2018-06-10Z' 
            and country = 'ee' 
          group by msisdn, pin_creation_time
      ) as T, json_array_elements(T.query_string) as query_string_values
    group by msisdn
  ) as T
  where T.pin_ever_valid
  order by first_valid_pin_creation_time desc
 )
  
select T.msisdn, T.first_valid_pin_creation_time, T.query_string, T.all_pins, T.pin_entry_deltas, R.* from T
full join R on T.msisdn = R.redshift_msisdn
order by COALESCE(T.first_valid_pin_creation_time, R.redshift_timestamp)
  ;

2.simpler.sql

WITH R AS (
  SELECT (case when strpos(R.msisdn, '372') = 1 then substr(R.msisdn, 4)  else R.msisdn end) as redshift_msisdn, R.msisdn as full_msisdn,
       R."timestamp" as redshift_timestamp
  FROM (
    SELECT *
    FROM dblink('redshift_server',$REDSHIFT$
       SELECT u.msisdn, u.timestamp from user_sessions u
        WHERE u.timestamp >= '2018-06-10Z'  
          AND u.offer_id = 853
          AND u.country_code = 'EE'
          AND u.sale
        ORDER BY u.timestamp
    $REDSHIFT$) AS t1 (msisdn text, timestamp timestamp)
   ) as R
),

T as (

  select * from (
  
      select 
          msisdn
        , count(pin) as pins
        , bool_or(pin_is_valid) as pin_ever_valid
        , min(creation_time) as first_lead_time
        , min(pin_creation_time) as first_pin_creation_time 
        , min(case when pin_is_valid then pin_creation_time else null end) as first_valid_pin_creation_time 
        , max(pin_creation_time) as last_pin_creation_time 
        , string_agg(pin, ',') as all_pins
        , array_agg(round( date_part('second', pin_delta))) as pin_entry_deltas
        , COALESCE(json_agg(query_string) FILTER (WHERE query_string IS NOT NULL), '[]') as query_string
    
      from (
          select 
              *
            , pin_creation_time - lag(pin_creation_time) over (partition by msisdn order by pin_creation_time) as pin_delta
          from berg_summary 
          where creation_time > '2018-06-10Z' 
            and country = 'ee' 
      ) as T
    group by msisdn
  ) as T
  where T.pin_ever_valid
  order by first_valid_pin_creation_time desc
 )
  
select T.msisdn, T.first_valid_pin_creation_time, T.query_string, T.all_pins, T.pin_entry_deltas, R.* from T
full join R on T.msisdn = R.redshift_msisdn
order by COALESCE(T.first_valid_pin_creation_time, R.redshift_timestamp)
  ;

3.sql

WITH R AS (
  SELECT (case when strpos(R.msisdn, '372') = 1 then substr(R.msisdn, 4)  else R.msisdn end) as redshift_msisdn, R.msisdn as full_msisdn,
       R."timestamp" as redshift_timestamp
  FROM (
    SELECT *
    FROM dblink('redshift_server',$REDSHIFT$
       SELECT u.msisdn, u.timestamp from user_sessions u
        WHERE u.timestamp >= '2018-06-10Z'  
          AND u.offer_id = 853
          AND u.country_code = 'EE'
          AND u.sale
        ORDER BY u.timestamp
    $REDSHIFT$) AS t1 (msisdn text, timestamp timestamp)
   ) as R
),

T as (

  select * from (
  
      select 
          msisdn
        , count(pin) as pins
        , bool_or(pin_is_valid) as pin_ever_valid
        , min(creation_time) as first_lead_time
        , min(pin_creation_time) as first_pin_creation_time 
        , min(case when pin_is_valid then pin_creation_time else null end) as first_valid_pin_creation_time 
        , max(pin_creation_time) as last_pin_creation_time 
        , string_agg(pin, ',') as all_pins
        , array_agg(round( date_part('second', pin_delta))) as pin_entry_deltas
        , json_agg(json_build_object('pin', pin, 'is_valid', pin_is_valid, 'delta', round( date_part('second', pin_delta)))) as pin_entry_details
        , COALESCE(json_agg(query_string) FILTER (WHERE query_string IS NOT NULL), '[]') as query_string
    
      from (
          select 
              *
            , pin_creation_time - lag(pin_creation_time) over (partition by msisdn order by pin_creation_time) as pin_delta
          from berg_summary 
          where creation_time > '2018-06-10Z' 
            and country = 'ee' 
      ) as T
    group by msisdn
  ) as T
  where T.pin_ever_valid
  order by first_valid_pin_creation_time desc
 )
  
select T.msisdn, T.first_valid_pin_creation_time, T.query_string, T.all_pins, T.pin_entry_deltas, T.pin_entry_details, R.* from T
full join R on T.msisdn = R.redshift_msisdn
order by COALESCE(T.first_valid_pin_creation_time, R.redshift_timestamp)
  ;