package mobvista.dmp.datasource.rtdmp.lazada

/**
 * @package: mobvista.dmp.datasource.rtdmp.lazada
 * @author: wangjf
 * @date: 2021/8/5
 * @time: 6:13 下午
 * @email: jinfeng.wang@mobvista.com
 */
object Constant {

  val etl_install_sql: String =
    """
      |SELECT campaign_id,gaid
      |  FROM dwh.ods_adn_trackingnew_install
      |  WHERE CONCAT(yyyy,mm,dd) = @dt AND UPPER(country_code) IN ('ID','PH')
      |  AND ext_campaignpackagename = 'com.lazada.android'
      |  GROUP BY campaign_id,gaid
      |""".stripMargin

  val etl_event_sql: String =
    """
      |SELECT campaign_id,gaid
      |  FROM dwh.ods_adn_tracking_ss_event
      |  WHERE CONCAT(yyyy,mm,dd) = @dt AND country in('ID','PH') AND event_name = 'REGISTRATION'
      |  GROUP BY campaign_id,gaid
      |""".stripMargin

  val merge_install_sql: String =
    """
      |SELECT
      |  COALESCE(t1.campaign_id, t2.campaign_id) campaign_id,
      |  COALESCE(t1.gaid, t2.gaid) gaid,
      |  COALESCE(t1.update_date, t2.update_date) update_date
      |  FROM
      |  (SELECT campaign_id, gaid, '@new_date' update_date
      |     FROM dwh.etl_adn_tracking_install
      |     WHERE dt = '@dt'
      |  ) t1
      |  FULL OUTER JOIN
      |  (SELECT campaign_id, gaid, update_date
      |     FROM dwh.merge_adn_tracking_install
      |     WHERE dt = '@before_date' AND update_date > '@update_date'
      |  ) t2
      |  ON t1.campaign_id = t2.campaign_id AND t1.gaid = t2.gaid
      |""".stripMargin

  val process_rtdmp_audience_sql: String =
    """
      |SELECT t2.gaid
      |  FROM
      |  (SELECT campaign_id,gaid
      |     FROM dwh.etl_adn_tracking_event WHERE dt = '@dt'
      |  ) t1
      |  RIGHT JOIN
      |  (SELECT campaign_id,gaid
      |     FROM dwh.merge_adn_tracking_install WHERE dt = '@dt'
      |  ) t2
      |  ON t1.campaign_id = t2.campaign_id AND t1.gaid = t2.gaid
      |  WHERE t1.gaid IS NULL
      |  GROUP BY t2.gaid
      |""".stripMargin
}