getCanceledSubFromAndroid.sql

SELECT
  sub_canceled_events.date,
  sub_canceled_events.subscription_id,
  sub_canceled_events.user_id,
  
  sub_created_events.client          AS creator_client,
  sub_created_events.installation_id AS creator_installation_id,
  sub_created_events.session_id      AS creator_session_id,
  sub_created_events.locale          AS creator_locale,
FROM events AS sub_canceled_events
LEFT JOIN (
  SELECT DISTINCT ON (subscription_id) *
  FROM events
  WHERE type = 'subscription_created'
  ORDER BY date DESC
) AS sub_created_events
ON sub_canceled_events.subscription_id = sub_created_events.subscription_id
WHERE
  sub_canceled_events.type = 'subscription_canceled'
  AND creator_client = 'android'