Postgresql scripts

.gitignore

.idea

21buttons.sql

select
message->>'device_id' as device_id,
message->>'app_version' as app_version,
message->>'condition' as condition,
message->>'date' as date,
message->>'type' as type
from sns_log
where
message->>'app_package' like 'com.android21buttons%'
and message->>'date' > '2017-03-31'
and message->>'type' <> 'FEATURES_DATA'
order by message->>'date' desc

README-postgres-tools.md

This is a toolset of postgresql scripts.

action-features.sql

select 
id,
message->>'date' as date,
message->>'device_id' as device,
message->>'condition' as condition,
message->>'app_version' as version,
message->>'type' as type,
-- message,
message #> '{"action", "prob_opportune_moment"}' as prob_action,
message #> '{"posted", "prob_opportune_moment"}' as prob_posted,
message #> '{"posted","features"}' as post_features,
message #> '{"action","features"}' as action_features
from sns_log
where
message->>'type' = 'ACTION'
and
 message ? 'date'
 and message->>'device_id' = '41ad44718426a2952851fd9cd82c0f'
order by 1 desc

action.sql

select 
	message->>'campaign_id' as campaign,
	message->>'content_id' as content,
	message->>'notification_id' as notification,
	message->>'handled_before' as before,
	message->>'sdk_notif_id' as sdk_notif_id,
	
	message->>'json_version' as json,
	message->>'model_version' as model,
	
	message->>'device_id' as device_id,
	message->>'client_id' as client_id,
	message->>'condition' as condition,
	
	message->>'app_package' as package,
	message->>'app_version' as version,
	
	message->'action'->>'ground_truth' as gt,
	message->'action'->>'prob_opportune_moment' as action_prob,
	
	message->'posted'->>'prob_opportune_moment' as post_prob,
	message->'posted' as posted_features,
	
	received   
from 
	sns_log
where 
	message->>'type' = 'ACTION'
	and received > '2017-10-01'
	

all-existing-devices.sql

select device_id,app_package 
from (select
message->>'device_id' as device_id,
message->>'app_package' as app_package,
1 as first
from sns_log as l
where message->>'app_package' like 'com.terra%'
union 
select
message->>'device_id' as device_id,
message->>'app_name' as app_package,
2 as second
from sns_log
where 
message->>'app_name' like 'com.terra%'
) as t
group by t.device_id,t.app_package

all-table-size.sql

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

apppackage.sql

select * from sns_log 
where message->>'app_package' like 'com.telefonica.snlibapp'
order by id asc

backup1.sql

bo-bulk_insert_customer.sql

-- Insert a bulk number of random customer_id
-- Organization is hardcoded to 1
--
insert into app_customer (
    client_id,
    organization_id
)
select
    md5(random()::text) || "_bulk", 1
from generate_series(1, 100000)

bo-customers_histogram.sql

select client_id , count(*)

from (
select 
 c.client_id, c.tm_registration,
 d.device_id,
 o.name
-- *
from 
	app_customer as c, 
	app_customer_devices as cd,
	app_device as d,
	organization as o
where 
c.organization_id = o.id
and o.name = 'Waken app'
and cd.client_id = c.id
and cd.device_id = d.id
order by c.client_id
) as f
group by client_id
 having count(*) > 3

bo-list-pending-notifications.sql

select d.device_id,nd.status
 ,n.title,n.body,n.image_url,n.campaign_id
from 
	(select * from app_device where device_id = '134134134') as d
inner join
	(select * from app_notification_device where status <> 'RECEIVED') as nd on d.id = nd.device_id
inner join
    app_notification as n on n.id = nd.notification_id
inner join
	app_campaign as c on c.id = n.campaign_id

bo-notifications-hist.sql

SELECT campaign_id, count(*) 
FROM APP_NOTIFICATION
where status in ('RECEIVED', 'PUSHED', 'SENT')
and campaign_id is not null
group by campaign_id
order by campaign_id

bo-uniq-enqueues-campaign.sql

select a.campaign_id,
count(*)
from (select 
(message->>'campaign_id'::text)::int as campaign_id,
message->>'device_id' as device_id
from sns_log
where 
message->>'type' = 'ENQUEUE'
and message->>'campaign_id' is not null
and message->>'campaign_id' not like 'manually%'
group by campaign_id, device_id
order by campaign_id, device_id) as a
group by campaign_id

client_events.sql

select 
count(*) as total,
--received,
message->>'event_type' as event
--message

from sns_log_testing

where message->>'type' = 'CLIENT_EVENT'

and id > 310 * 100000
group by event

dash_aggregation_filter.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE VIEW dash_aggregation_filter AS  SELECT DISTINCT ON (dash_preprocess.app_package, dash_preprocess.campaign_id, dash_preprocess.client_id, dash_preprocess.device_id, dash_preprocess.type) dash_preprocess.id,
    dash_preprocess.time_received,
    dash_preprocess.time_generated,
    dash_preprocess.client_id,
    dash_preprocess.device_id,
    dash_preprocess.type,
    dash_preprocess.app_package,
    dash_preprocess.sdk_build_date,
    dash_preprocess.app_version,
    dash_preprocess.campaign_id,
    dash_preprocess.clicked,
    dash_preprocess.condition,
    dash_preprocess.model_version
   FROM dash_preprocess
  WHERE (dash_preprocess.type = ANY (ARRAY['ENQUEUE'::text, 'POSTED'::text, 'ACTION'::text, 'MODEL_UPDATED'::text, 'CLIENT_INFO'::text])) AND dash_preprocess.campaign_id IS NOT NULL AND dash_preprocess.condition IS NOT NULL;

dash_create_sns_log_testing.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE sns_log_testing (
    id bigint DEFAULT nextval('sns_log_id_seq'::regclass) PRIMARY KEY,
    message jsonb,
    received timestamp with time zone NOT NULL DEFAULT now(),
    normalized boolean DEFAULT false,
    version_normalized character(16) DEFAULT ''::bpchar
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX sns_log_testing_pkey ON sns_log_testing(id int8_ops);
CREATE INDEX sns_log_testing_expr_idx ON sns_log_testing((message ->> 'app_package'::text) text_ops);
CREATE INDEX sns_log_testing_expr_idx1 ON sns_log_testing((message ->> 'app_version'::text) text_ops);
CREATE INDEX sns_log_testing_expr_idx2 ON sns_log_testing((message ->> 'campaign_id'::text) text_ops);
CREATE INDEX sns_log_testing_expr_idx3 ON sns_log_testing((message ->> 'date'::text) text_ops);
CREATE INDEX sns_log_testing_expr_idx4 ON sns_log_testing((message ->> 'device_id'::text) text_ops);
CREATE INDEX sns_log_testing_expr_idx5 ON sns_log_testing((message ->> 'type'::text) text_ops);
CREATE INDEX sns_log_testing_version_normalized_idx ON sns_log_testing(version_normalized bpchar_ops);

dash_create_test_preprocess.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE test_preprocess (
    id bigint,
    time_received timestamp with time zone,
    time_generated timestamp without time zone,
    client_id text,
    device_id text,
    type text,
    app_package text,
    sdk_build_date text,
    app_version text,
    campaign_id text,
    clicked boolean,
    condition text,
    model_version text,
    is_opportune_moment boolean,
    prob_opportune_moment double precision,
    platform text
);

-- Indices -------------------------------------------------------

CREATE INDEX test_preprocess_id_idx ON test_preprocess(id int8_ops);
CREATE INDEX test_preprocess_time_generated_idx ON test_preprocess(time_generated timestamp_ops);
CREATE INDEX test_preprocess_time_received_idx ON test_preprocess(time_received timestamptz_ops);
CREATE INDEX test_preprocess_type_idx ON test_preprocess(type text_ops);
CREATE INDEX test_preprocess_app_package_idx ON test_preprocess(app_package text_ops);
CREATE INDEX test_preprocess_client_id_idx ON test_preprocess(client_id text_ops);
CREATE INDEX test_preprocess_device_id_idx ON test_preprocess(device_id text_ops);
CREATE INDEX test_preprocess_time_received_hour_idx ON test_preprocess((date_trunc('hour'::text, timezone('UTC'::text, time_received))) timestamp_ops);
CREATE INDEX test_preprocess_time_generated_hour_idx ON test_preprocess((date_trunc('hour'::text, time_generated)) timestamp_ops);

dash_diagnostics_backoffice.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE MATERIALIZED VIEW dash_diagnostics_backoffice AS  SELECT app_notification.campaign_id,
    app_license.name AS app_package,
    COALESCE(app_device.token_provider, 'OTHER'::tokenproviderenum) AS token_provider,
    COALESCE(app_notification.delivery_status, 'null'::character varying) AS delivery_status,
    COALESCE(app_notification.device_status, 'null'::character varying) AS device_status,
    COALESCE((app_notification.notif_meta -> 'device'::text) ->> 'sdk_build_date'::text, 'null'::text) AS sdk_build_date,
    count(DISTINCT app_notification.id) AS value
   FROM app_notification
     JOIN app_device ON app_notification.device_id = app_device.id
     JOIN app_license ON app_device.license_id = app_license.id
  GROUP BY app_notification.campaign_id, app_license.name, (COALESCE(app_device.token_provider, 'OTHER'::tokenproviderenum)), (COALESCE(app_notification.delivery_status, 'null'::character varying)), (COALESCE(app_notification.device_status, 'null'::character varying)), (COALESCE((app_notification.notif_meta -> 'device'::text) ->> 'sdk_build_date'::text, 'null'::text));

-- Indices -------------------------------------------------------

CREATE INDEX dash_diagnostics_backoffice_app_package_idx ON dash_diagnostics_backoffice(app_package text_ops);
CREATE UNIQUE INDEX dash_diagnostics_backoffice_campaign_id_app_package_token_provi ON dash_diagnostics_backoffice(campaign_id int4_ops,app_package text_ops,token_provider enum_ops,delivery_status text_ops,device_status text_ops,sdk_build_date text_ops);
CREATE INDEX dash_diagnostics_backoffice_campaign_id_idx ON dash_diagnostics_backoffice(campaign_id int4_ops);
CREATE INDEX dash_diagnostics_backoffice_delivery_status_idx ON dash_diagnostics_backoffice(delivery_status text_ops);
CREATE INDEX dash_diagnostics_backoffice_device_status_idx ON dash_diagnostics_backoffice(device_status text_ops);
CREATE INDEX dash_diagnostics_backoffice_sdk_build_date_idx ON dash_diagnostics_backoffice(sdk_build_date text_ops);
CREATE INDEX dash_diagnostics_backoffice_token_provider_idx ON dash_diagnostics_backoffice(token_provider enum_ops);

dash_diagnostics_campaign.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE MATERIALIZED VIEW dash_diagnostics_campaign AS  SELECT date_trunc('hour'::text, dash_aggregation_filter.time_generated) AS "time",
    dash_aggregation_filter.app_package,
    dash_aggregation_filter.campaign_id,
    dash_aggregation_filter.condition,
    dash_aggregation_filter.model_version,
    dash_aggregation_filter.sdk_build_date,
    dash_aggregation_filter.type,
    count(dash_aggregation_filter.id) AS count_value,
    sum(dash_aggregation_filter.clicked::integer) AS click_value
   FROM dash_aggregation_filter
  GROUP BY (date_trunc('hour'::text, dash_aggregation_filter.time_generated)), dash_aggregation_filter.app_package, dash_aggregation_filter.campaign_id, dash_aggregation_filter.condition, dash_aggregation_filter.model_version, dash_aggregation_filter.sdk_build_date, dash_aggregation_filter.type;

-- Indices -------------------------------------------------------

CREATE INDEX dash_diagnostics_campaign_app_package_idx ON dash_diagnostics_campaign(app_package text_ops);
CREATE INDEX dash_diagnostics_campaign_campaign_id_idx ON dash_diagnostics_campaign(campaign_id text_ops);
CREATE INDEX dash_diagnostics_campaign_condition_idx ON dash_diagnostics_campaign(condition text_ops);
CREATE INDEX dash_diagnostics_campaign_model_version_idx ON dash_diagnostics_campaign(model_version text_ops);
CREATE INDEX dash_diagnostics_campaign_sdk_build_date_idx ON dash_diagnostics_campaign(sdk_build_date text_ops);
CREATE INDEX dash_diagnostics_campaign_time_idx ON dash_diagnostics_campaign(time timestamp_ops);
CREATE INDEX dash_diagnostics_campaign_type_idx ON dash_diagnostics_campaign(type text_ops);
CREATE UNIQUE INDEX dash_diagnostics_campaign_unique_idx ON dash_diagnostics_campaign(time timestamp_ops,app_package text_ops,campaign_id text_ops,condition text_ops,model_version text_ops,sdk_build_date text_ops,type text_ops);

dash_diagnostics_global.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE MATERIALIZED VIEW dash_diagnostics_global AS  SELECT date_trunc('hour'::text, dash_aggregation_filter.time_generated) AS "time",
    dash_aggregation_filter.condition,
    dash_aggregation_filter.model_version,
    dash_aggregation_filter.sdk_build_date,
    dash_aggregation_filter.type,
    count(dash_aggregation_filter.id) AS count_value,
    sum(dash_aggregation_filter.clicked::integer) AS click_value
   FROM dash_aggregation_filter
  GROUP BY (date_trunc('hour'::text, dash_aggregation_filter.time_generated)), dash_aggregation_filter.condition, dash_aggregation_filter.model_version, dash_aggregation_filter.sdk_build_date, dash_aggregation_filter.type;

-- Indices -------------------------------------------------------

CREATE INDEX dash_diagnostics_global_condition_idx ON dash_diagnostics_global(condition text_ops);
CREATE INDEX dash_diagnostics_global_model_version_idx ON dash_diagnostics_global(model_version text_ops);
CREATE INDEX dash_diagnostics_global_sdk_build_date_idx ON dash_diagnostics_global(sdk_build_date text_ops);
CREATE UNIQUE INDEX dash_diagnostics_global_unique_idx ON dash_diagnostics_global(time timestamp_ops,condition text_ops,model_version text_ops,sdk_build_date text_ops,type text_ops);
CREATE INDEX dash_diagnostics_global_time_idx ON dash_diagnostics_global(time timestamp_ops);
CREATE INDEX dash_diagnostics_global_type_idx ON dash_diagnostics_global(type text_ops);

dash_diagnostics_license.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE MATERIALIZED VIEW dash_diagnostics_license AS  SELECT date_trunc('hour'::text, dash_aggregation_filter.time_generated) AS "time",
    dash_aggregation_filter.app_package,
    dash_aggregation_filter.condition,
    dash_aggregation_filter.model_version,
    dash_aggregation_filter.sdk_build_date,
    dash_aggregation_filter.type,
    count(dash_aggregation_filter.id) AS count_value,
    sum(dash_aggregation_filter.clicked::integer) AS click_value
   FROM dash_aggregation_filter
  GROUP BY (date_trunc('hour'::text, dash_aggregation_filter.time_generated)), dash_aggregation_filter.app_package, dash_aggregation_filter.condition, dash_aggregation_filter.model_version, dash_aggregation_filter.sdk_build_date, dash_aggregation_filter.type;

-- Indices -------------------------------------------------------

CREATE INDEX dash_diagnostics_license_app_package_idx ON dash_diagnostics_license(app_package text_ops);
CREATE INDEX dash_diagnostics_license_condition_idx ON dash_diagnostics_license(condition text_ops);
CREATE INDEX dash_diagnostics_license_model_version_idx ON dash_diagnostics_license(model_version text_ops);
CREATE INDEX dash_diagnostics_license_sdk_build_date_idx ON dash_diagnostics_license(sdk_build_date text_ops);
CREATE INDEX dash_diagnostics_license_time_idx ON dash_diagnostics_license(time timestamp_ops);
CREATE INDEX dash_diagnostics_license_type_idx ON dash_diagnostics_license(type text_ops);
CREATE UNIQUE INDEX dash_diagnostics_license_unique_idx ON dash_diagnostics_license(time timestamp_ops,app_package text_ops,condition text_ops,model_version text_ops,sdk_build_date text_ops,type text_ops);

dash_preprocess.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE MATERIALIZED VIEW dash_preprocess AS  SELECT sns_log_testing.id,
    sns_log_testing.received AS time_received,
    timezone('CET'::text, (sns_log_testing.message ->> 'date'::text)::timestamp with time zone) AS time_generated,
    sns_log_testing.message ->> 'client_id'::text AS client_id,
    sns_log_testing.message ->> 'device_id'::text AS device_id,
    sns_log_testing.message ->> 'type'::text AS type,
    COALESCE(sns_log_testing.message ->> 'app_package'::text, 'null'::text) AS app_package,
    COALESCE(sns_log_testing.message ->> 'sdk_build_date'::text, (sns_log_testing.message -> 'headers_'::text) ->> 'X-Snlib-Builddate'::text, 'null'::text) AS sdk_build_date,
    sns_log_testing.message ->> 'app_version'::text AS app_version,
    sns_log_testing.message ->> 'campaign_id'::text AS campaign_id,
    ((sns_log_testing.message -> 'action'::text) ->> 'ground_truth'::text)::boolean AS clicked,
    decode_condition(sns_log_testing.message ->> 'condition'::text) AS condition,
    sns_log_testing.message ->> 'model_version'::text AS model_version
   FROM sns_log_testing
  WHERE (sns_log_testing.message ->> 'date'::text) IS NOT NULL AND (sns_log_testing.message ->> 'client_id'::text) IS NOT NULL AND (sns_log_testing.message ->> 'device_id'::text) IS NOT NULL AND (sns_log_testing.message ->> 'type'::text) IS NOT NULL;

-- Indices -------------------------------------------------------

CREATE INDEX dash_preprocess_app_package_idx ON dash_preprocess(app_package text_ops);
CREATE INDEX dash_preprocess_client_id_idx ON dash_preprocess(client_id text_ops);
CREATE INDEX dash_preprocess_device_id_idx ON dash_preprocess(device_id text_ops);
CREATE UNIQUE INDEX dash_preprocess_id_idx ON dash_preprocess(id int8_ops);
CREATE INDEX dash_preprocess_time_generated_idx ON dash_preprocess(time_generated timestamp_ops);
CREATE INDEX dash_preprocess_time_received_idx ON dash_preprocess(time_received timestamptz_ops);
CREATE INDEX dash_preprocess_type_idx ON dash_preprocess(type text_ops);

dashboard_types.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE VIEW dashboard_types AS  SELECT count(*) AS total,
    date_trunc('hour'::text, timezone('UTC'::text, dash_preprocess.time_received)) AS "time",
    dash_preprocess.app_package,
    dash_preprocess.type,
    dash_preprocess.sdk_build_date,
    dash_preprocess.app_version
   FROM dash_preprocess
  GROUP BY dash_preprocess.app_package, dash_preprocess.type, (date_trunc('hour'::text, timezone('UTC'::text, dash_preprocess.time_received))), dash_preprocess.sdk_build_date, dash_preprocess.app_version;

dashboard_types_generated.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE VIEW dashboard_types_generated AS  SELECT count(*) AS total,
    date_trunc('hour'::text, timezone('UTC'::text, test_preprocess.time_generated)) AS "time",
    test_preprocess.app_package,
    test_preprocess.type,
    test_preprocess.sdk_build_date,
    test_preprocess.app_version
   FROM test_preprocess
  GROUP BY test_preprocess.app_package, test_preprocess.type, (date_trunc('hour'::text, timezone('UTC'::text, test_preprocess.time_generated))), test_preprocess.sdk_build_date, test_preprocess.app_version;

delta-posted-vs-action-event.sql

with tdelta as(

select 
message->>'device_id' as device_id, 
id, 
message->>'date' as date, 
received, 
cast(message->'action'->>'date' as timestamp with time zone) - cast(message->'posted'->>'date' as timestamp with time zone) as delta, 
message from sns_log
where message ? 'date'
and message->>'type' = 'ACTION'
and message->>'app_package' = 'com.terra.appcreator.downloadsvivo'
order by delta
)
SELECT count(device_id),
extract(day from tdelta.delta) as delta_days

FROM tdelta
group by extract(day from tdelta.delta)
order by 2,1

delta-received-event-histogram.sql

with tdelta as(

select 
message->>'device_id' as device_id, 
id, 
message->>'date' as date, 
received, 
received - cast(message->>'date' as timestamp with time zone) as delta, 
message from sns_log
where message ? 'date'
and message->>'app_package' = 'com.terra.appcreator.downloadsvivo'
and received - cast(message->>'date' as timestamp with time zone)  > '1 day'::interval
order by delta
)
SELECT count(device_id),
extract(day from tdelta.delta) as days

FROM tdelta
group by extract(day from tdelta.delta)
order by 1, 2

delta-received-event.sql

select 
message->>'device_id' as device_id, 
id, 
message->>'date' as date, 
received, 
received - cast(message->>'date' as timestamp with time zone) as delta, 
message from sns_log
where message ? 'date'
and message->>'app_package' = 'com.terra.appcreator.downloadsvivo'
and received - cast(message->>'date' as timestamp with time zone)  > '1 day'::interval
order by delta

detail-exception.sql

select 
id, received,
message->>'date' as date,
message->>'device_id' as device,
message->>'client_id' as cliente,
message->>'app_package' as package,
message->>'stack_trace' as excp,
split_part(message->>'stack_trace', ':', 1) as exception

from sns_log
where

id >= 281*100000

and message->>'type' = 'EXCEPTION'
and split_part(message->>'stack_trace', ':', 1) = 'java.lang.Exception'
-- and message->>'stack_trace' not like '%wakelock%'
-- and id <= 28479264

device-birthdate.sql

select 
	message->>'device_id' as device_id,
	message->>'condition' as condition,
	message->>'app_package' as app_package,
	min(message->>'date') as birthdate 
from 
	sns_log
where
	message->>'device_id' is not null and
	message->>'condition' is not null and
	message->>'app_package' is not null
	
group by 
	device_id,
	condition,
	app_package
order by
	birthdate asc

devices.sql

select message->>'device_id' as device_id, count(message->>'device_id') from sns_log group by message->>'device_id';

different-notifications.sql

SELECT 
count(*) as count,
message->>'notification_content' as notif
from sns_log
where
message->>'app_package' = 'com.terra.appcreator.downloadsvivo'
and message->>'app_version' = '20204000'
and message->>'type' = 'ENQUEUE'

group by
 message->>'notification_content'
order by count desc

duplicates.sql

with dev_cli as (select
--message->>'platform' platform,
--message->>'app_package' package,
message->>'device_id' device,
message->>'client_id' client,
true
from sns_log
where TRUE
--and received < '2018-07-30 16:00'
and message ->> 'platform' is not null
and message->>'app_package' = any( values 
-- ('com.movistar.ar.base'),
-- ('com.movistar.cl.base'), 
('com.movistar.cl.miwifi')
-- ('ar.com.movistar.base')
)
group by message->>'device_id', message->>'client_id'
)

select * from  dev_cli as t1

where (select count(*) from  dev_cli as t2

where t1.client = t2.client) > 1

enqueue-posted-action-dash.sql

select 
	enqueues.id as enq_id, 
	 posted.id as post_id, 
	 actioned.id as act_id, 
    -- Campaign info
	enqueues.campaign_id as enq_campaign, 
	-- posted.campaign as post_campaign, 
	-- actioned.campaign as act_campaign, 

--	enqueues.post_after_timeout as post_after_timeout, 
--	enqueues.notification_timeout as notification_timeout, 
	
	-- Device Info
	enqueues.device_id as enq_device, 
	enqueues.client_id as enq_device, 
	posted.device_id post_device,
	posted.client_id post_client,
	actioned.device_id act_device,
	actioned.client_id act_client,

	enqueues.platform as enq_platform, 
--	user_notifs,
	enqueues.sdk_build_date as enq_sdk_build_date, 

	-- Conditions
	enqueues.condition as enq_condition, 
	posted.condition as post_condition, 
	actioned.condition as act_condition, 

	-- Received
	enqueues.received enq_received, 
	posted.received post_received, 
	actioned.received act_received, 
	
	-- Dates
	enqueues.date as enq_date, 
	posted.date as post_date, 
	actioned.date as act_date, 
	
	-- Prob
	posted.is_opportune_moment as post_is_opportune_moment, 
	posted.prob_opportune_moment as post_prob_opportune_moment, 

--	actioned.act_act_prob_opportune_moment,
--	actioned.act_post_prob_opportune_moment,
	1

from
-- ENQUEUE
(
select 
id,
campaign_id,
device_id,
client_id,
time_generated as date,
platform,

condition,
sdk_build_date,

-- message->>'type' as tipo,
time_received as received

from dash_preprocess
where dash_preprocess.type = 'ENQUEUE'
and id > 710 * 100000
) as enqueues

left join
-- POSTED
(
select 
id,
campaign_id,
device_id,
client_id,
time_generated as date,
platform,

condition,
sdk_build_date,

is_opportune_moment,
prob_opportune_moment,


-- message->>'type' as tipo,
time_received as received
from dash_preprocess

where dash_preprocess.type = 'POSTED'
and id > 710 * 100000

) as posted

on enqueues.device_id = posted.device_id

left join
-- ACTION
(
select 

id,
campaign_id,
device_id,
client_id,
time_generated as date,
platform,

condition,
sdk_build_date,

is_opportune_moment,
prob_opportune_moment,


-- message->>'type' as tipo,
time_received as received
from dash_preprocess

where dash_preprocess.type = 'ACTION'
and id > 710 * 100000

) as actioned

on posted.device_id = actioned.device_id

-- where actioned.device is not null
-- and posted.device is not null
order by 1,2,3

enqueue-posted-action.sql

select 
	enqueues.id as enq_id, 
	 posted.id as post_id, 
	 actioned.id as act_id, 
    -- Campaign info
	enqueues.campaign as enq_campaign, 
	-- posted.campaign as post_campaign, 
	-- actioned.campaign as act_campaign, 

	enqueues.post_after_timeout as post_after_timeout, 
	enqueues.notification_timeout as notification_timeout, 
	
	-- Device Info
	enqueues.device as enq_device, 
	enqueues.client as enq_device, 
	posted.device post_device,
	posted.client post_client,
	actioned.device act_device,
	actioned.client act_client,

	enqueues.platform as enq_platform, 
	user_notifs,
	enqueues.sdk_build_date as enq_sdk_build_date, 

	-- Conditions
	enqueues.condition as enq_condition, 
	posted.condition as post_condition, 
	actioned.condition as act_condition, 

	-- Received
	enqueues.received enq_received, 
	posted.received post_received, 
	actioned.received act_received, 
	
	-- Dates
	enqueues.date as enq_date, 
	posted.date as post_date, 
	actioned.date as act_date, 
	
	-- Prob
	posted.is_opportune_moment as post_is_opportune_moment, 
	posted.prob_opportune_moment as post_prob_opportune_moment, 

	actioned.act_act_prob_opportune_moment,
	actioned.act_post_prob_opportune_moment,
	1

from
-- ENQUEUE
(
select 
id,
message->>'campaign_id' as campaign,
message->>'post_after_timeout' as post_after_timeout,
message->>'notification_timeout' as notification_timeout,

message->>'device_id' as device,
message->>'client_id' as client,
message->>'date' as date,
message->>'platform' as platform,

message->>'condition' as condition,
message->'statistics'->>'user_notifs_settings' as user_notifs,
message->>'sdk_build_date' as sdk_build_date,

-- message->>'type' as tipo,
received

from sns_log
where message->>'type' = 'ENQUEUE'
and id > 710 * 100000
) as enqueues

left join
-- POSTED
(
select 
id,
message->>'device_id' as device,
message->>'client_id' as client,
message->>'date' as date,
message->>'campaign_id' as campaign,
message->>'condition' as condition,
message->>'is_opportune_moment' as is_opportune_moment,
message->>'prob_opportune_moment' as prob_opportune_moment,


-- message->>'type' as tipo,
received
from sns_log

where message->>'type' = 'POSTED'
and id > 710 * 100000

) as posted

on enqueues.device = posted.device

left join
-- ACTION
(
select 
id,
message->>'device_id' as device,
message->>'client_id' as client,
message->>'campaign_id' as campaign,
message->>'date' as date,
message->>'condition' as condition,
message->'action'->>'ground_truth' as gt,
message->'action'->>'prob_opportune_moment' as act_act_prob_opportune_moment,
message->'posted'->>'prob_opportune_moment' as act_post_prob_opportune_moment,


-- message->>'type' as tipo,
received
from sns_log

where message->>'type' = 'ACTION'
and id > 710 * 100000

) as actioned

on posted.device = actioned.device

-- where actioned.device is not null
-- and posted.device is not null
order by 1,2,3

exception-histogram-date-license.sql

select 
count(*),
date_trunc('day', received) as time,
message->>'app_package' as app_package,
split_part(message->>'stack_trace', ':', 1) as exception

from sns_log 

where message->>'type' = 'EXCEPTION'


group by
app_package,
exception,
time

exception-histogram.sql

select 
count(*),
split_part(message->>'stack_trace', ':', 1) as exception

from sns_log
where

id >= 281*100000

and message->>'type' = 'EXCEPTION'

-- and id <= 28479264

group by exception

exceptions.sql

select 
-- message->>'stack_trace',
message->>'app_package' as package,
count(message->>'app_package') as total,
split_part(message->>'stack_trace', ':', 1) as exception
from sns_log
where 
	message->>'type' = 'EXCEPTION'
-- and message->>'app_package' = 'com.alarmclock.wakenapp'
-- and message->>'app_package' = 'es.franciscojrp.boredomfighter'
-- and message->>'app_package' = 'com.telefonica.BoredomFighter'
-- and message->>'app_package' = 'com.telefonica.snlibapp'

group by exception, package 

firebase-token.sql

select 
message->>'type' as type,
message->>'app_version' as app_version,
message->>'phone_model' as model,
message->>'device_id' as device_id,
cast(message->>'date' as timestamp with time zone) as date,
extract(timezone from (message->>'date')::timestamp with time zone) as zone,
message->>'firebase_token' as token,
message
from sns_log
where message->>'type' = 'FIREBASE_TOKEN'

first-notif-date.sql

with messages as (select
message->>'type' as type,
message->>'notification_content' as notif,
message->>'device_id' as device_id,
message->>'condition' as condition,
message->>'date' as date
from
sns_log
where message->>'app_package' = 'com.terra.appcreator.downloadsvivo'
and message->>'app_version' = '20204000')
select count(*),
notif, condition,
min(date)
from messages
where type = 'ENQUEUE'
group by notif, condition
order by min(date)

hist-clicked-notification.sql

select distinct
message->>'condition' as condition,
message->'action'->'ground_truth' as gt,
count(*) as total
from sns_log
where
--message->>'app_package' = 'com.android21buttons%'
--message->>'app_package' = 'es.franciscojrp.boredomfighter'
--message->>'app_package' = 'com.telefonica.BoredomFighter'
message->>'campaign_id' = '44395'
--and message->>'date' > '2017-04-01'
and message->>'type' = 'ACTION'
group by message->>'condition',
message->'action'->'ground_truth'

hist-devices-customers-license.sql

select
message->>'platform' platform,
message->>'app_package' package,
count(distinct message->>'device_id') dev,
count(distinct message->>'client_id') client,
count(distinct message->>'client_id')* 100.0/count(distinct message->>'device_id') as perc
from sns_log
where TRUE
--and received < '2018-08-21 12:00'
-- and id > 420 * 100000
and message->>'app_package' = any( values 
('com.movistar.ar.base'),
('ar.com.movistar.base'),
('com.movistar.cl.base'), 
('com.movistar.cl.miwifi'),
('br.com.vivo.smart_wifi'),
('br.com.vivo.smartwifi'),
('com.movistar.pe.base'),
('com.movistar.co.base')
)
and message -> 'platform' is not null
group by message->>'app_package', message->>'platform' 

hist-type-campaign.sql

select distinct
message->>'condition' as condition,
message->>'app_package' as package,
message->>'type' as type,
count(*) as total
from sns_log
where
message->>'app_package' = 'com.alarmclock.wakenapp'
and message->>'campaign_id' = '41131'
--message->>'app_package' = 'es.franciscojrp.boredomfighter'
--message->>'app_package' = 'com.telefonica.BoredomFighter'
group by 
	message->>'condition', 
	message->>'type',
	package
order by type

hist-type.sql

select distinct
--message->>'condition' as condition,
message->>'app_package' as package,
message->>'type' as type,
count(*) as total
from sns_log
where
--message->>'app_package' = 'com.alarmclock.wakenapp'
--message->>'app_package' = 'es.franciscojrp.boredomfighter'
message->>'app_package' = 'com.telefonica.BoredomFighter'
group by 
--	message->>'condition', 
	message->>'type',
	package
order by type

jsonb_update.sql

--select id from 
--
--sns_log
--
--where
--message->>'date' = '2018-009-028T10:36:28.441-0300'

update sns_log set message = jsonb_set(message,'{date}', '"2018-09-28T10:36:28.441-0300"') where id = 11598274;

last-insert.sql

select received, last

from sns_log, 
(select max(id) as last from sns_log) as f
where id = last

latest-by-type.sql

select 
message->>'type' as action
, message->>'device_id' as device_id
, max(cast(message->>'date' as timestamp without time zone))
from sns_log
where message->>'type' = 'ACTION'
group by
message->>'type', message->>'device_id'

latest-clicked.sql

select 
message->>'type' as action
, message->>'app_package' as app
, message->>'device_id' as device_id
, max(cast(message->>'date' as timestamp without time zone))
from sns_log
where message->>'type' = 'ACTION'
and not(cast(message->'action'->>'ground_truth' as integer) is null or cast(message->'action'->>'ground_truth' as integer) = 0)
group by
message->>'type', message->>'device_id', message->>'app_package'

latest-per-device.sql

select
message->>'device_id' as device_id,
(message->'statistics'->>'total_enqueued')::int as total_enqueued,
(message->'statistics'->>'total_posted')::int as total_posted,
(message->'statistics'->>'total_clicked')::int as total_clicked,
(message->'statistics'->>'total_dismissed')::int as total_dismissed,
(message->'statistics'->>'posted_notifs_queue_size')::int as posted_notifs_queue_size,
(message->'statistics'->>'queued_notifs_queue_size')::int as queued_notifs_queue_size,
message->>'app_version' as app_version,
message->>'date' as date,
message->>'condition' as condition,
received,
message->>'app_package' as app_package,
message->>'type' as type,
message
from sns_log
where message->>'date' is not null
and message->>'device_id' = '41ad44718426a2952851fd9cd82c0f'
order by date desc

latest-received-time.sql

select 
	distinct message->>'device_id' device_id,
    max(received) over (partition by message->>'device_id')
--	distinct message->>'client_id' client_id,
--    max(received) over (partition by message->>'client_id')
from
	sns_log
where 
	message->>'app_package' = 'com.alarmclock.wakenapp'

latest-stats-per-device.sql

select
message->>'device_id' as device_id,
(message->>'condition')::int as condition,
message->>'app_package' as app_package,
-- message->'statistics' as statistics,
(message->'statistics'->>'total_enqueued')::int as total_enqueued,
(message->'statistics'->>'total_posted')::int as total_posted,
(message->'statistics'->>'total_clicked')::int as total_clicked,
(message->'statistics'->>'total_dismissed')::int as total_dismissed,
(message->'statistics'->>'posted_notifs_queue_size')::int as posted_notifs_queue_size,
(message->'statistics'->>'queued_notifs_queue_size')::int as queued_notifs_queue_size,
message->>'date' as date,
received
from 
sns_log as lft
join (
-- select the latest date we received a message for a device,condition,package
select
	message->>'device_id' as device_id,
	(message->>'condition')::int as condition,
	message->>'app_package' as app_package,
--	message->>'type' as type,
	max(message->>'date') as date
from sns_log
where 
	message ? 'device_id'
	and message ? 'condition'
	and message ? 'app_package'
	and message ? 'statistics'
--	and normalized = FALSE
group by device_id, condition, app_package --, type
) sq
on (sq.device_id = message->>'device_id'
and sq.condition = (message->>'condition')::int
and sq.app_package = message->>'app_package'
and sq.date = message->>'date'
)
where 
app_package = 'com.terra.appcreator.downloadsvivo'
order by device_id, date desc

list-all-exceptions.sql

select 
received,
message->>'date' as date,
message->>'device_id' as device,
message->>'client_id' as client,
message->>'condition' as condition,
message->>'app_version' as version,
message->>'type' as type,
message->>'app_package' as app_package, 
split_part(message->>'stack_trace', ':', 1) as exception,
message->>'stack_trace' as trace
from sns_log
where
-- message->>'app_package' = 'com.terra.appcreator.downloadsvivo'
message->>'app_package' = 'com.alarmclock.wakenapp'
--and message ? 'date'
and message->>'type' = 'EXCEPTION'
order by 1 desc

list-all-tag-names.sql

select 

distinct jsonb_object_keys(message->'headers_') as keys,
1


from sns_log_testing

where message ? 'headers_'
--and
--message->>'type' not in ( 'DEVICE_EVENT', 'CLIENT)

list-bad-dates.sql

/*
    This script will list all the entries that does not match the format `YYYYMMDDTHH:mm:ss`.

    It will output a list of records with format `id TAB wrong-date` like:


        1765452	2018-08-06T021:19:29.588-0300
        3211673	2018-08-017T13:27:03.831-0300
        3211674	2018-08-17T0013:27:03.831-0300
        4085337	2018-08-22T0015:53:45.0793-0300

    We recommend you keep the `id` for later auditing.
*/


select
    sns_log_testing.id,
    sns_log_testing.message ->> 'date' as orig
--    sns_log_testing.message
from
    sns_log_testing
where
    message->>'date' !~
    '[[:digit:]]{1,4}-[[:digit:]]{1,2}-[[:digit:]]{1,2}T[[:digit:]]{1,2}:[[:digit:]]{1,2}:[[:digit:]]{1,2}'

    and
        id > 738 * 100000
--    and
--        id < 291 * 100000

lost-enqueued.sql

select
message->>'device_id' as device_id,
(message->>'condition')::int as condition,
message->>'app_package' as app_package,
message->>'date' as date,
-- message->'statistics' as statistics,
(message->'statistics'->>'total_enqueued')::int as total_enqueued,
(message->'statistics'->>'total_posted')::int as total_posted,
(message->'statistics'->>'total_clicked')::int as total_clicked,
(message->'statistics'->>'total_dismissed')::int as total_dismissed,
(message->'statistics'->>'posted_notifs_queue_size')::int as posted_notifs_queue_size,
(message->'statistics'->>'queued_notifs_queue_size')::int as queued_notifs_queue_size,
(message->'statistics'->>'total_queued')::int as total_queued
from 
sns_log as lft
join (
-- select the latest date we received a message for a device,condition,package
select
	message->>'device_id' as device_id,
	(message->>'condition')::int as condition,
	message->>'app_package' as app_package,
--	message->>'type' as type,
	max(message->>'date') as date
from sns_log
where 
	message ? 'device_id'
	and message ? 'condition'
	and message ? 'app_package'
	and message ? 'statistics'
--	and normalized = FALSE
group by device_id, condition, app_package --, type
) sq
on (sq.device_id = message->>'device_id'
and sq.condition = (message->>'condition')::int
and sq.app_package = message->>'app_package'
and sq.date = message->>'date'
)
where (message->'statistics'->>'total_posted')::int <> (message->'statistics'->>'total_enqueued')::int and
app_package = 'com.terra.appcreator.downloadsvivo'
order by device_id, date desc

main-sns_log_size-2.sql

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

main-sns_log_size-3.sql

SELECT l.what, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / x.ct END          AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   sns_log t  -- provide table name *once*
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl))
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
    , (true , 'indexes_size'                     , pg_indexes_size(tbl))
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
    , (true , 'live_rows_in_text_representation' , txt_len)
    , (false, '------------------------------'   , NULL)
    , (false, 'row_count'                        , ct)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, what, nr);

main-sns_log_size.sql

WITH x AS (
   SELECT count(*)               AS ct
        , sum(length(t::text))   AS txt_len  -- length in characters
        , 'sns_log'::regclass AS tbl  -- provide (qualified) table name here
   FROM   sns_log t  -- ... and here
   )
, y AS (
   SELECT ARRAY [pg_relation_size(tbl)
               , pg_relation_size(tbl, 'vm')
               , pg_relation_size(tbl, 'fsm')
               , pg_table_size(tbl)
               , pg_indexes_size(tbl)
               , pg_total_relation_size(tbl)
               , txt_len
             ] AS val
        , ARRAY ['core_relation_size'
               , 'visibility_map'
               , 'free_space_map'
               , 'table_size_incl_toast'
               , 'indexes_size'
               , 'total_size_incl_toast_and_indexes'
               , 'live_rows_in_text_representation'
             ] AS name
   FROM   x
   )
SELECT unnest(name)                AS what
     , unnest(val)                 AS "bytes/ct"
     , pg_size_pretty(unnest(val)) AS bytes_pretty
     , unnest(val) / ct            AS bytes_per_row
FROM   x, y

UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;

max-table-size.sql

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

model_update_daily_histogram.sql

with t as (
select
	max(time_received) received,
	max(time_generated) generated,
	device_id, client_id, model_version
from
	test_preprocess

where
    type = 'MODEL_UPDATED'
    and id > 340 * 100000

group by 
	device_id, client_id, model_version
	order by 3
)	

select

extract('day' from received) as day,
extract('month' from received) as month,
model_version,
count(device_id) as value
from t
group by 
1, 2, 3

model_update_histogram.sql

select
	max(time_received) received,
	max(time_generated) generated,
	device_id, client_id, model_version
from
	test_preprocess

where
    type = 'MODEL_UPDATED'
    and id > 340 * 100000

group by 
	device_id, client_id, model_version
	order by 3
	

normalize-single-query-2.sql

select
app_package,
condition,
sum(total_clicked) as total_clicked,
sum(total_dismissed) as total_dismissed,
sum(total_enqueued) as total_enqueued,
sum(total_posted) as total_posted
from(
select
message->>'device_id' as device_id,
(message->>'condition')::int as condition,
message->>'app_package' as app_package,
message->>'date' as date,
-- message->'statistics' as statistics,
(message->'statistics'->>'total_posted')::int as total_posted,
(message->'statistics'->>'total_clicked')::int as total_clicked,
(message->'statistics'->>'total_dismissed')::int as total_dismissed,
(message->'statistics'->>'posted_notifs_queue_size')::int as posted_notifs_queue_size,
(message->'statistics'->>'queued_notifs_queue_size')::int as queued_notifs_queue_size,
(message->'statistics'->>'total_enqueued')::int as total_enqueued,
(message->'statistics'->>'total_queued')::int as total_queued
from 
sns_log as lft
join (
-- select the latest date we received a message for a device,condition,package
select
	message->>'device_id' as device_id,
	(message->>'condition')::int as condition,
	message->>'app_package' as app_package,
	max(message->>'date') as date
from sns_log
where 
	message ? 'device_id'
	and message ? 'condition'
	and message ? 'app_package'
	and message ? 'statistics'
--	and normalized = FALSE
group by device_id, condition, app_package
) sq
on (sq.device_id = message->>'device_id'
and sq.condition = (message->>'condition')::int
and sq.app_package = message->>'app_package'
and sq.date = message->>'date'
)
) as f
group by app_package, condition


-- max(message->'statistics'->>'total_clicked') as total_clicked,
-- max(message->'statistics'->>'total_dismissed') as total_dismissed,
-- max(message->'statistics'->>'posted_notifs_queue_size') as posted_notifs_queue_size,
-- max(message->'statistics'->>'queued_notifs_queue_size') as queued_notifs_queue_size,
-- max(message->'statistics'->>'total_enqueued') as total_enqueued,
-- max(message->'statistics'->>'total_queued') as total_queued

normalize-single-query.sql

select
message->>'device_id' as device_id,
message->>'condition' as condition,
message->>'app_package' as app_package,
max(message->>'date') as date,
max(message->'statistics'->>'total_posted') as total_posted,
max(message->'statistics'->>'total_clicked') as total_clicked,
max(message->'statistics'->>'total_dismissed') as total_dismissed,
max(message->'statistics'->>'posted_notifs_queue_size') as posted_notifs_queue_size,
max(message->'statistics'->>'queued_notifs_queue_size') as queued_notifs_queue_size,
max(message->'statistics'->>'total_enqueued') as total_enqueued,
max(message->'statistics'->>'total_queued') as total_queued

from 
sns_log
where 
	message ? 'device_id'
	and message ? 'condition'
	and message ? 'app_package'
--	and message ? 'statistics'
group by device_id, condition, app_package

ordered-logs.sql

select 
message->>'date' as date,
message->>'device_id' as device,
message->>'condition' as condition,
message->>'app_version' as version,
message->>'type' as type,
(message->'statistics'->>'total_enqueued')::int as total_enqueued,
(message->'statistics'->>'total_posted')::int as total_posted,
(message->'statistics'->>'total_clicked')::int as total_clicked,
(message->'statistics'->>'total_dismissed')::int as total_dismissed,
(message->'statistics'->>'posted_notifs_queue_size')::int as posted_notifs_queue_size,
(message->'statistics'->>'queued_notifs_queue_size')::int as queued_notifs_queue_size,
message->>'app_package' as app_package
from sns_log
where
message->>'app_package' = 'com.terra.appcreator.downloadsvivo'
and message ? 'date'
order by 1 desc

ordered-stats.sql

select
id,
message->'statistics'->>'total_enqueued' as enqueued,
message->'statistics'->>'total_posted' as posted,
message->'statistics'->>'total_clicked' as clicked,
message->'statistics'->>'total_dismissed' as dismissed,
(message->'statistics'->>'posted_notifs_queue_size')::int as posted_notifs_queue_size,
(message->'statistics'->>'queued_notifs_queue_size')::int as queued_notifs_queue_size,
message->>'device_id' as device_id,
message->>'condition' as condition,
message->>'date' as date, 
received,
message->>'type' as type,
message->>'app_version' as app_version
-- message
from sns_log
where
(message->>'app_package' = 'com.terra.appcreator.downloadsvivo'
or message->>'app_name' = 'com.terra.appcreator.downloadsvivo') 
-- and (message->>'type'='ENQUEUE' or message->>'type'='ACTION')
order by date desc, device_id

pending-features.sql

SELECT sns_log.id, 
sns_log.message->>'app_package' as package,
sns_log.message ->> 'device_id' AS device_id, 
sns_log.message as message, 
message->>'action' as action, 
message->>'posted' as posted, 
message->>'data_action' as data_action, 
message->>'data_posted' as data_posted
FROM sns_log 
WHERE message ?| array['type', 'data_action', 'data_posted', 'action', 'posted']
and (message->>'type' = 'ACTION')
order by id asc

phone-models-histogram.sql

select 
count( DISTINCT message ->> 'device_id') AS device,
 message ->> 'type' AS type,
  message ->> 'phone_model' AS MODEL
from sns_log
where message ? 'phone_model'
-- and message->>'type' <> 'EXCEPTION'
group by message ->> 'phone_model', message->>'type'
ORDER BY 1 desc

platform_exceptions.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE platform_exceptions (
    id bigint,
    time_received timestamp with time zone,
    time_generated timestamp without time zone,
    client_id text,
    device_id text,
    platform text,
    app_package text,
    app_version text,
    phone_model text,
    json_version text,
    sdk_build_date text,
    supported_abis text,
    platform_version text,
    exception text,
    trace text
);

-- Indices -------------------------------------------------------

CREATE INDEX platform_exceptions_id_idx ON platform_exceptions(id int8_ops);
CREATE INDEX platform_exceptions_time_received_idx ON platform_exceptions(time_received timestamptz_ops);
CREATE INDEX platform_exceptions_time_generated_idx ON platform_exceptions(time_generated timestamp_ops);
CREATE INDEX platform_exceptions_client_id_idx ON platform_exceptions(client_id text_ops);
CREATE INDEX platform_exceptions_device_id_idx ON platform_exceptions(device_id text_ops);
CREATE INDEX platform_exceptions_platform_idx ON platform_exceptions(platform text_ops);
CREATE INDEX platform_exceptions_app_package_idx ON platform_exceptions(app_package text_ops);
CREATE INDEX platform_exceptions_app_version_idx ON platform_exceptions(app_version text_ops);
CREATE INDEX platform_exceptions_phone_model_idx ON platform_exceptions(phone_model text_ops);
CREATE INDEX platform_exceptions_sdk_build_date_idx ON platform_exceptions(sdk_build_date text_ops);
CREATE INDEX platform_exceptions_platform_version_idx ON platform_exceptions(platform_version text_ops);
CREATE INDEX platform_exceptions_exception_idx ON platform_exceptions(exception text_ops);
CREATE INDEX platform_exceptions_trace_idx ON platform_exceptions(trace text_ops);
CREATE INDEX platform_exceptions_time_received_hour_idx ON platform_exceptions((date_trunc('hour'::text, timezone('UTC'::text, time_received))) timestamp_ops);

platform_models.sql

-- DDL generated by Postico 1.5.3
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE platform_models (
    id bigint,
    time_received timestamp with time zone,
    time_generated timestamp without time zone,
    client_id text,
    device_id text,
    platform text,
    app_package text,
    app_version text,
    phone_model text,
    json_version text,
    sdk_build_date text,
    model_version text,
    platform_version text
);

-- Indices -------------------------------------------------------

CREATE INDEX platform_models_id_idx ON platform_models(id int8_ops);
CREATE INDEX platform_models_time_received_idx ON platform_models(time_received timestamptz_ops);
CREATE INDEX platform_models_time_generated_idx ON platform_models(time_generated timestamp_ops);
CREATE INDEX platform_models_client_id_idx ON platform_models(client_id text_ops);
CREATE INDEX platform_models_device_id_idx ON platform_models(device_id text_ops);
CREATE INDEX platform_models_platform_idx ON platform_models(platform text_ops);
CREATE INDEX platform_models_app_package_idx ON platform_models(app_package text_ops);
CREATE INDEX platform_models_app_version_idx ON platform_models(app_version text_ops);
CREATE INDEX platform_models_phone_model_idx ON platform_models(phone_model text_ops);
CREATE INDEX platform_models_sdk_build_date_idx ON platform_models(sdk_build_date text_ops);
CREATE INDEX platform_models_platform_version_idx ON platform_models(platform_version text_ops);
CREATE INDEX platform_models_model_version_idx ON platform_models(model_version text_ops);
CREATE INDEX platform_models_time_received_hour_idx ON platform_models((date_trunc('hour'::text, timezone('UTC'::text, time_received))) timestamp_ops);

probabilities.sql

select message->'action'->>'date' as date, 
message->>'app_version' as app_version, 
message->>'device_id' as device_id, 
message->'action'->>'prob_opportune_moment' as prob_opportune_moment
from sns_log
where message->'action'->>'prob_opportune_moment' is not null
order by id
asc

remove-duplicates.sql

SELECT 
id, message,
rnum,
message->>'device_id' as device_id,
message->>'condition' as condition

FROM (SELECT 
		id,
		message,
        ROW_NUMBER() OVER (partition BY message ORDER BY id) AS rnum
     FROM 
     	sns_log
     where
		message->>'app_version' ='20204000' and
		message->>'app_package' ='com.terra.appcreator.downloadsvivo'
     	
     	) as t
WHERE t.rnum > 1
order by device_id

research-action.sql

select 
	actioned.id as act_id, 
	actioned.license as act_license, 

	actioned.device act_device,
	actioned.client act_client,
	actioned.condition as act_condition, 

	actioned.campaign as act_campaign, 
	actioned.clicked act_clicked, 
	actioned.act_act_prob_opportune_moment,
	actioned.act_post_prob_opportune_moment,

	actioned.received act_received, 	
	actioned.date as act_date


from
-- ACTION
(
select 
id,
message->>'app_package' as license,
message->>'device_id' as device,
message->>'client_id' as client,
message->>'campaign_id' as campaign,
message->>'date' as date,
message->>'condition' as condition,
message->'action'->>'ground_truth' as clicked,
message->'action'->>'prob_opportune_moment' as act_act_prob_opportune_moment,
message->'posted'->>'prob_opportune_moment' as act_post_prob_opportune_moment,


-- message->>'type' as tipo,
received
from sns_log

where message->>'type' = 'ACTION'
and id > 710 * 100000

) as actioned

research-enqueue.sql

select 
	enqueues.id as enq_id, 
	enqueues.license as enq_license, 
	enqueues.campaign as enq_campaign, 
	enqueues.post_after_timeout as post_after_timeout, 
	enqueues.notification_timeout as notification_timeout, 
	
	enqueues.device as enq_device, 
	enqueues.client as enq_client, 
	enqueues.platform as enq_platform, 
	user_notifs,
	enqueues.sdk_build_date as enq_sdk_build_date, 

	enqueues.condition as enq_condition, 
	enqueues.received enq_received, 
	enqueues.date as enq_date

from
-- ENQUEUE
(
select 
id,
message->>'campaign_id' as campaign,
message->>'app_package' as license,
message->>'post_after_timeout' as post_after_timeout,
message->>'notification_timeout' as notification_timeout,

message->>'device_id' as device,
message->>'client_id' as client,
message->>'date' as date,
message->>'platform' as platform,

message->>'condition' as condition,
message->'statistics'->>'user_notifs_settings' as user_notifs,
message->>'sdk_build_date' as sdk_build_date,

-- message->>'type' as tipo,
received

from sns_log
where message->>'type' = 'ENQUEUE'
and id > 710 * 100000
) as enqueues

research-info.sql

select 
	info.id as info_id, 
	info.license as info_license, 

	info.device info_device,
	info.client info_client,

	s.message->>'platform' as platform,
	s.message->'info_array' as info,
	
	s.received info_received, 	
	s.message->>'date' as date

from
(
select 
max(id) id,
message->>'app_package' as license,
message->>'device_id' as device,
message->>'client_id' as client
from sns_log

where message->>'type' = 'CLIENT_INFO'
and id > 710 * 100000

group by 2, 3, 4
) as info,
sns_log as s

where info.id = s.id

research-model.sql

select 
	model_updated.id as modelupdated_id, 
	model_updated.license modelupdated_license,
	model_updated.model_version modelupdated_model_version,

	model_updated.device modelupdated_device,
	model_updated.client modelupdated_client,
	model_updated.platform as modelupdated_platform, 
	model_updated.sdk_build_date as modelupdated_sdk_build_date,
	
	model_updated.received modelupdated_received, 
	model_updated.date as modelupdated_date
	-- Prob

from
-- model_updated
(
select 
id,
message->>'app_package' as license,
message->>'model_version' as model_version,

message->>'device_id' as device,
message->>'client_id' as client,
message->>'date' as date,
message->>'platform' as platform,

message->>'condition' as condition,
message->'statistics'->>'user_notifs_settings' as user_notifs,
message->>'sdk_build_date' as sdk_build_date,
message->>'is_opportune_moment' as is_opportune_moment,
message->>'prob_opportune_moment' as prob_opportune_moment,

-- message->>'type' as tipo,
received

from sns_log
where message->>'type' = 'MODEL_UPDATED'
and id > 710 * 100000
) as model_updated

research-posted.sql

select 
	posted.id as post_id, 
	posted.license post_license,

	posted.campaign as post_campaign, 

	posted.device post_device,
	posted.client post_client,
	posted.platform as post_platform, 
	posted.condition as post_condition, 
	user_notifs,
	posted.sdk_build_date as post_sdk_build_date,
	
	posted.is_opportune_moment as post_is_opportune_moment, 
	posted.prob_opportune_moment as post_prob_opportune_moment, 

	posted.received post_received, 
	posted.date as post_date
	-- Prob

from
-- POSTED
(
select 
id,
message->>'app_package' as license,
message->>'campaign_id' as campaign,
message->>'post_after_timeout' as post_after_timeout,
message->>'notification_timeout' as notification_timeout,

message->>'device_id' as device,
message->>'client_id' as client,
message->>'date' as date,
message->>'platform' as platform,

message->>'condition' as condition,
message->'statistics'->>'user_notifs_settings' as user_notifs,
message->>'sdk_build_date' as sdk_build_date,
message->>'is_opportune_moment' as is_opportune_moment,
message->>'prob_opportune_moment' as prob_opportune_moment,

-- message->>'type' as tipo,
received

from sns_log
where message->>'type' = 'POSTED'
and id > 710 * 100000
) as posted

reset-token-version.sql

UPDATE sns_log
    SET version_normalized='', normalized=False
where message->>'type' like '%TOKEN%'

sanity-checks.sql

select 
message->>'type' as type,
message->>'date' as date,
message->>'app_package' as package,
received,
message
from sns_log
where not(message ? 'date')
order by date desc;


select 
message->>'type' as type,
message->>'date' as date,
message->>'app_package' as package,
received,
message
from sns_log
where not(message ? 'type')
order by date desc;

stats-per-hour-2.sql

SELECT 
-- message->>'device_id' as device_id,
message->>'condition' as condition,
coalesce(message->>'app_package', message->>'app_name') as app_package,
message->>'app_version' as version,
date_part('year', cast(message->>'date' as timestamp with time zone)) as year, 
date_part('month', cast(message->>'date' as timestamp with time zone)) as month, 
date_part('day', cast(message->>'date' as timestamp with time zone)) as day, 
date_part('hour', cast(message->>'date' as timestamp with time zone)) as hour,
sum((message->'statistics'->>'total_enqueued')::int) AS total_enqueued,
sum((message->'statistics'->>'total_posted')::int) AS total_posted,
sum((message->'statistics'->>'total_clicked')::int) AS total_clicked,
sum((message->'statistics'->>'total_dismissed')::int) AS total_dismissed
FROM sns_log
where
message->>'app_package' = 'com.terra.appcreator.downloadsvivo'
GROUP BY 
date_part('year', cast(message->>'date' as timestamp with time zone)), 
date_part('month', cast(message->>'date' as timestamp with time zone)), 
date_part('day', cast(message->>'date' as timestamp with time zone)), 
date_part('hour', cast(message->>'date' as timestamp with time zone)),
message->>'app_version',
coalesce(message->>'app_package', message->>'app_name'),
message->>'condition',
message->>'device_id'
order by
-- message->>'device_id',
date_part('year', cast(message->>'date' as timestamp with time zone)) desc, 
date_part('month', cast(message->>'date' as timestamp with time zone)) desc, 
date_part('day', cast(message->>'date' as timestamp with time zone)) desc, 
date_part('hour', cast(message->>'date' as timestamp with time zone)) desc

stats-per-hour-3.sql

SELECT 
-- message->>'device_id' as device_id,
message->>'condition' as condition,
coalesce(message->>'app_package', message->>'app_name') as app_package,
message->>'app_version' as version,
date_part('year', cast(message->>'date' as timestamp with time zone)) as year, 
date_part('month', cast(message->>'date' as timestamp with time zone)) as month, 
date_part('day', cast(message->>'date' as timestamp with time zone)) as day, 
date_part('hour', cast(message->>'date' as timestamp with time zone)) as hour,
sum((message->'statistics'->>'total_enqueued')::int) AS total_enqueued,
sum((message->'statistics'->>'total_posted')::int) AS total_posted,
sum((message->'statistics'->>'total_clicked')::int) AS total_clicked,
sum((message->'statistics'->>'total_dismissed')::int) AS total_dismissed
FROM sns_log
where
message->>'app_package' = 'com.terra.appcreator.downloadsvivo'
GROUP BY 
date_part('year', cast(message->>'date' as timestamp with time zone)), 
date_part('month', cast(message->>'date' as timestamp with time zone)), 
date_part('day', cast(message->>'date' as timestamp with time zone)), 
date_part('hour', cast(message->>'date' as timestamp with time zone)),
message->>'app_version',
coalesce(message->>'app_package', message->>'app_name'),
message->>'condition'
-- message->>'device_id'
order by
-- message->>'device_id',
date_part('year', cast(message->>'date' as timestamp with time zone)) desc, 
date_part('month', cast(message->>'date' as timestamp with time zone)) desc, 
date_part('day', cast(message->>'date' as timestamp with time zone)) desc, 
date_part('hour', cast(message->>'date' as timestamp with time zone)) desc

stats-per-hour.sql

SELECT 
message->>'device_id' as device_id,
message->>'condition' as condition,
message->>'app_package' as app_package,
date_part('year', cast(message->>'date' as timestamp with time zone)) as year, 
date_part('month', cast(message->>'date' as timestamp with time zone)) as month, 
date_part('day', cast(message->>'date' as timestamp with time zone)) as day, 
date_part('hour', cast(message->>'date' as timestamp with time zone)) as hour,
     max((message->'statistics'->>'total_enqueued')::int) AS total_enqueued
FROM sns_log
GROUP BY 
date_part('year', cast(message->>'date' as timestamp with time zone)), 
date_part('month', cast(message->>'date' as timestamp with time zone)), 
date_part('day', cast(message->>'date' as timestamp with time zone)), 
date_part('hour', cast(message->>'date' as timestamp with time zone)),
message->>'app_package',
message->>'condition',
message->>'device_id'
order by
message->>'device_id',
date_part('year', cast(message->>'date' as timestamp with time zone)) desc, 
date_part('month', cast(message->>'date' as timestamp with time zone)) desc, 
date_part('day', cast(message->>'date' as timestamp with time zone)) desc, 
date_part('hour', cast(message->>'date' as timestamp with time zone)) desc

table-size.sql

SELECT l.what, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / x.ct END          AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.sns_log t 
   WHERE message->>'type' = 'DEVICE_EVENT' -- provide table name *once*
   and message->>'event_type' = 'UNINSTALL'
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl))
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
    , (true , 'indexes_size'                     , pg_indexes_size(tbl))
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
    , (true , 'live_rows_in_text_representation' , txt_len)
    , (false, '------------------------------'   , NULL)
    , (false, 'row_count'                        , ct)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, what, nr);

table_size_timeseries.sql

select *,
pg_size_pretty(sum("bytes/ct") over ()) as total
from (

SELECT l.what, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / x.ct END          AS bytes_per_row
     , mydate
FROM  (
   SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
        , date_trunc('day', received) AS myday
   FROM   public.sns_log t 
   WHERE received > '2018-10-22' and received < '2018-10-29'
   group by date_trunc('day', received)
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl), myday)
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'), myday)
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'), myday)
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl), myday)
    , (true , 'indexes_size'                     , pg_indexes_size(tbl), myday)
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl), myday)
    , (true , 'live_rows_in_text_representation' , txt_len, myday)
    , (false, '------------------------------'   , NULL, myday)
    , (false, 'row_count'                        , ct, myday)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl), myday)
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl), myday)
   ) l(is_size, what, nr, mydate)
   ) as r
   
   where r.what = 'live_rows_in_text_representation'

tests.sql

select 
received,
message,
message->>'device_id' as device_id,
message->>'type' as type

from sns_log
where 
message->>'device_id' like '%test%'
or
message->>'device_id' like '%-performance%'
or
message ? 'test'

total-clicked-2.sql

    with partial as 
    (
        SELECT 
            sns_log.message -> 'device_id' AS device_id, 
            sns_log.message -> 'app_package' AS app_package,
            sns_log.message -> 'app_version' AS app_version, 
            sns_log.message -> 'condition' AS condition,
            message->'action'->>'ground_truth' as clicked
        FROM sns_log 
        WHERE (
        sns_log.message ->> 'app_version') = '20204000'
        AND (sns_log.message ->> 'app_package') = 'com.terra.appcreator.downloadsvivo'
        AND (sns_log.message ->> 'type') = 'ACTION'
        AND sns_log.message ? 'app_package'
        AND sns_log.message ? 'app_version'
        AND sns_log.message ? 'condition' 
        AND sns_log.message ? 'device_id'
        GROUP BY 
            sns_log.message -> 'app_package',
            sns_log.message -> 'app_version', 
            sns_log.message -> 'condition',
            sns_log.message -> 'device_id',
			message->'action'->>'ground_truth'
        ORDER BY
			app_package,
            condition,
			clicked
        )
    SELECT 
        count(partial.device_id) AS count,
        partial.app_package, 
        partial.app_version,
        partial.condition,
        partial.clicked
    FROM 
        partial 
    GROUP BY 
        partial.app_package, 
        partial.app_version,
        partial.condition,
        partial.clicked

total-clicked-period.sql

select
count(*) as total,
message->>'condition' as condition,
message->>'app_package' as app_package,
message->>'app_version' as app_version,
message->'action'->>'ground_truth' as clicked

from sns_log
where message->>'date' is not null
and message->>'app_package' ='com.terra.appcreator.downloadsvivo'
-- and message->>'app_version' ='39'
and message->>'type' ='ACTION'
AND (message->>'date')::timestamp with time zone > '2017-03-09'
group by
message->>'condition' ,
message->>'app_package',
message->>'app_version',
message->'action'->>'ground_truth'

total-clicked.sql

select
count(*) as total,
message->>'condition' as condition,
message->>'app_package' as app_package,
message->>'app_version' as app_version,
message->'action'->>'ground_truth' as clicked

from sns_log
where message->>'date' is not null
and message->>'app_package' ='com.terra.appcreator.downloadsvivo'
and message->>'app_version' ='20204000'
and message->>'type' ='ACTION'

group by
message->>'condition' ,
message->>'app_package',
message->>'app_version',
message->'action'->>'ground_truth'

total-participants.sql

    with partial as 
    (
        SELECT 
            sns_log.message -> 'device_id' AS device, 
            sns_log.message -> 'app_package' AS app_package,
            sns_log.message -> 'app_version' AS app_version, 
            sns_log.message -> 'condition' AS condition
        FROM sns_log 
        WHERE (
        sns_log.message ->> 'app_version') = '20204000'
        AND (sns_log.message ->> 'app_package') = 'com.terra.appcreator.downloadsvivo'
        AND (sns_log.message ->> 'type') = 'ENQUEUE'
        AND sns_log.message ? 'app_package'
        AND sns_log.message ? 'app_version'
        AND sns_log.message ? 'condition' 
        AND sns_log.message ? 'device_id'
        GROUP BY 
            sns_log.message -> 'app_package',
            sns_log.message -> 'app_version', 
            sns_log.message -> 'condition',
            sns_log.message -> 'device_id' 
        ORDER BY
            sns_log.message -> 'condition'
        )
    SELECT 
        count(partial.device) AS count,
        partial.app_package, 
        partial.app_version,
        partial.condition
    FROM 
        partial 
    GROUP BY 
        partial.app_package, 
        partial.app_version,
        partial.condition

total-posted.sql

select
count(*) as total_posted,
message->>'condition' as condition,
message->>'app_package' as app_package,
message->>'app_version' as app_version

from sns_log
where message->>'date' is not null
and message->>'app_package' ='com.terra.appcreator.downloadsvivo'
and message->>'app_version' ='20204000'
and message->>'type' ='POSTED'

group by
message->>'condition' ,
message->>'app_package',
message->>'app_version'

type-groups.sql

select count(message->>'type'), message->>'type' as type
from sns_log
group by type;

type.sql

select * from sns_log 
where message->>'type' like 'ACTION'
order by id asc

uniq-devices.sql

select 
  message->>'device_id' device_id
from
	sns_log
where 
	message->>'app_package' = 'com.alarmclock.wakenapp'
group by
    device_id
--select 
--	distinct message->>'device_id' device_id
--from
--	sns_log
--where 
--	message->>'app_package' = 'com.alarmclock.wakenapp'

update-bad-dates.sql

update

 sns_log_testing as sns_log

 set message = jsonb_set(message, '{date}', t.nueva::jsonb)
from
( select * from 
( values

('53415', '2018-07-0025T17:0057:32.350-0300', '"2018-07-25T17:57:32.350-0300"'),
('1595727', '2018-08-005T17:24:25.014-0400', '"2018-08-05T17:24:25.014-0400"'),
('2005129', '2018-0008-0008T13:42:08.712-0400', '"2018-08-08T13:42:08.712-0400"'),
('2070867', '2018-08-0008T21:41:10.960-0400', '"2018-08-08T21:41:10.960-0400"'),
('5675409', '2018-08-0031T0003:00:41.845-0300', '"2018-08-31T03:00:41.845-0300"'),
('6180233', '2018-0009-0002T21:18:31.891-0300', '"2018-09-02T21:18:31.891-0300"'),
('14142932', '2018-10-0003T14:37:26.145-0300', '"2018-10-03T14:37:26.145-0300"'),
('14427283', '2018-10-003T23:19:20.634+0200', '"2018-10-03T23:19:20.634+0200"'),
('15259473', '2018-10-004T07:15:14.960-0300', '"2018-10-04T07:15:14.960-0300"'),
('15259474', '2018-10-0004T07:15:14.960-0300', '"2018-10-04T07:15:14.960-0300"')

)
as t (id, orig, nueva) ) as t


where sns_log.id = t.id::bigint

update-json.sql

UPDATE sns_log
    SET message=jsonb_set(message, '{app_package}', '"com.telefonica.snlibapp"')
where message->>'type' = 'FIREBASE_TOKEN'

utc-times.sql

select message->>'device_id' as device_id, 
min(message->>'date') as pilot_birth_date, 
cast(min(message->>'date') as timestamp ) AT TIME ZONE 'utc' as pilot_birth_date_utc from sns_log
group by message->>'device_id'
order by pilot_birth_date

version-compare.sql

SELECT string_to_array('0.1.0', '.')::int[] AS current_version
	, string_to_array(version_normalized, '.')::int[] AS latest_version
	,(string_to_array('0.1.0', '.')::int[] > string_to_array(version_normalized, '.')::int[]) AS cmp
FROM   sns_log;

weird-stats-2.sql

select message->>'type' as type
	, message->>'app_version' as app_version
	, message->>'device_id' as device_id
	, message
from sns_log 
where 
	cast(message->'statistics'->>'total_enqueued' as integer) 
		- cast(message->'statistics'->>'total_posted' as integer)
		- cast(message->'statistics'->>'queued_notifs_queue_size' as integer) <> 0 
	and message->>'device_id' not like 'bbac%' 
	and message->>'device_id' not like '359a%';

weird-stats-3.sql

select message->>'type' as type
	, message->>'app_version' as app_version
	, message->>'device_id' as device_id
	, message
from sns_log 
where 
	cast(message->'statistics'->>'total_enqueued' as integer) 
		- cast(message->'statistics'->>'total_posted' as integer)
		- cast(message->'statistics'->>'queued_notifs_queue_size' as integer) <> 0 
	and message->>'device_id' not like 'bbac%' 
	and message->>'device_id' not like '359a%';

weird-stats.sql

select message, message->'device_id' as device_id from sns_log where cast(message->'statistics'->>'queued_notifs_queue_size' as integer) > 0 and message->>'device_id' not like 'bbac%' and message->>'device_id' not like '359a%';