Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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)
;
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)
;
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)
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.