1. WITH users AS (
  2.     SELECT DISTINCT uid
  3.     FROM analytics.parent_actions
  4.     WHERE dt >= today() - INTERVAL 14 DAY
  5.       AND dt < today()
  6.       AND notEmpty(open)
  7. ),
  8. payments AS (
  9.     SELECT DISTINCT uid
  10.     FROM analytics.money
  11.     WHERE ChargeNumber >= 1
  12.       AND NOT was_refunded
  13.       AND USDNet >= 0.25
  14.       AND ContractCharge_TM >= (today() - INTERVAL 10 DAY)
  15. )
  16. SELECT
  17.     pp.uid,
  18.     pp.id,
  19.     pp.region,
  20.     pp.ip_country,
  21.     pp.device_language,
  22.     pp.last_active_datetime::date as last_active_date
  23. FROM analytics.parent_properties AS pp
  24. JOIN users ON users.uid = pp.uid
  25. JOIN payments ON payments.uid = pp.uid
  26. WHERE pp.is_full_register
  27.   AND pp.full_reg_datetime >= '2000-01-01'
  28.   AND pp.platform = 'Android'
  29.   AND region in ('none', 'global', 'ru')
  30.   AND id >= 1000
  31. ORDER BY 2, 1;