API tools faq
paste
Guest User

Untitled

a guest
Jul 16th, 2018
67
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 19.96 KB | None | 0 0
raw download clone embed print report
  1. WITH
  2. cte0_unaggr AS (
  3. SELECT DISTINCT
  4. gr.mrr_date
  5. , gr.account_id AS billing_account_id
  6. , gr.product_line
  7. , pd.product_type_derived
  8. , COALESCE(pd.suite_ind, 'N') AS suite_ind
  9. , cd.sales_model
  10. , zam.salesamendmenttype__c
  11. , crm.dod_mrr_diff_type_transfer_adj
  12. , gross_mrr_prior_day
  13. , gross_mrr
  14. , gross_mrr_dod_diff
  15. , recurring_discount_prior_day
  16. , recurring_discount
  17. , recurring_discount_dod_diff
  18. , nonrecurring_discount_prior_day
  19. , nonrecurring_discount
  20. , nonrecurring_discount_dod_diff
  21. , net_mrr_prior_day
  22. , net_mrr
  23. , net_mrr_dod_diff
  24. , gr.baseplan_quantity_dod_diff
  25. , gr.addon_quantity_dod_diff
  26. , currentterm_prior_day
  27. , gr.currentterm
  28. , gr.billingperiod
  29. , gr.base_product_plan_prior_day
  30. , gr.base_product_plan
  31. , product_addon
  32. , crm_product_line_paid_combo_prior_day
  33. , crm_product_line_paid_combo
  34. , cd.crm_account_id
  35. , gr.original_rpc_id
  36. , gr.productrateplancharge_id
  37. , REPLACE(zac.masteraccountid__c, '.0', '') AS masteraccountid__c
  38. , gr.currency
  39. , cd.initiatedby
  40. , zac.paymentterm
  41. , REPLACE(cd.zendesk_account_id, '.0', '') AS zendesk_account_id
  42. , REPLACE(zac.zopimnumber__c, '.0', '') AS zopim_account_id
  43. FROM
  44. `edw-prod-153420.SourceData.dod_mrr_granular` gr
  45. JOIN
  46. `edw-prod-153420.SourceData.official_system_date_mrr_date_mapping` sys
  47. ON gr.mrr_date = sys.mrr_date
  48. LEFT JOIN
  49. `edw-prod-153420.SourceData.customer_dim_scd2` cd
  50. ON gr.account_id = cd.billing_account_id
  51. AND sys.system_date BETWEEN DATE(cd.dw_eff_start) AND DATE(cd.dw_eff_end)
  52. LEFT JOIN
  53. `edw-prod-153420.SourceData.dod_mrr_crm` crm
  54. ON gr.mrr_date = crm.mrr_date
  55. AND sys.mrr_date = crm.mrr_date
  56. AND cd.crm_account_id = crm.crm_account_id
  57. LEFT JOIN
  58. `edw-prod-153420.SourceData.product_dim_scd2` pd
  59. ON gr.productrateplancharge_id = pd.productrateplancharge_id
  60. AND sys.system_date BETWEEN DATE(pd.dw_eff_start) AND DATE(pd.dw_eff_end)
  61. LEFT JOIN
  62. `edw-prod-153420.SourceData.zuora_account_scd2` zac
  63. ON zac.id = cd.billing_account_id
  64. AND zac.dw_curr_ind = 'Y'
  65. LEFT JOIN
  66. `edw-prod-153420.SourceData.zuora_amendment_scd2` zam
  67. ON gr.amendment_id = zam.id
  68. AND zam.dw_curr_ind = 'Y'
  69. WHERE
  70. CONCAT(CAST(EXTRACT(YEAR FROM gr.mrr_date) AS STRING), CAST(EXTRACT(MONTH FROM gr.mrr_date) AS STRING))
  71. = (SELECT DISTINCT CASE WHEN CURRENT_DATE > MIN(system_date) THEN CONCAT(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS STRING), CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS STRING))
  72. ELSE CONCAT(CAST(EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE, INTERVAL 15 DAY)) AS STRING), CAST(EXTRACT(MONTH FROM DATE_SUB(CURRENT_DATE, INTERVAL 15 DAY)) AS STRING)) END AS yyyym
  73. FROM `edw-prod-153420.SourceData.official_system_date_mrr_date_mapping`
  74. WHERE
  75. CONCAT(CAST(EXTRACT(YEAR FROM mrr_date) AS STRING), CAST(EXTRACT(MONTH FROM mrr_date) AS STRING))
  76. = CONCAT(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS STRING), CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS STRING))
  77. GROUP BY CONCAT(CAST(EXTRACT(YEAR FROM mrr_date) AS STRING), CAST(EXTRACT(MONTH FROM mrr_date) AS STRING)))
  78. AND
  79. (gr.net_mrr_dod_diff != 0 OR gr.gross_mrr_dod_diff != 0)
  80. AND
  81. (cd.sales_model != 'Self-service'
  82. AND
  83. (zam.salesamendmenttype__c != 'EasyAgentAdd'
  84. OR gr.gross_mrr_dod_diff <= 0
  85. OR gr.baseplan_quantity_dod_diff <= 0))
  86. )
  87. ,cte1a_aggr_BA_PL_PT_main AS (
  88. SELECT DISTINCT
  89. mrr_date
  90. , billing_account_id
  91. , product_line
  92. , product_type_derived
  93. , suite_ind
  94. , SUM(COALESCE(gross_mrr_prior_day, 0)) AS gross_mrr_prior_day
  95. , SUM(COALESCE(gross_mrr, 0)) AS gross_mrr
  96. , SUM(COALESCE(gross_mrr_dod_diff, 0)) AS gross_mrr_dod_diff
  97. , SUM(COALESCE(recurring_discount_prior_day, 0)) AS recurring_discount_prior_day
  98. , SUM(COALESCE(recurring_discount, 0)) AS recurring_discount
  99. , SUM(COALESCE(recurring_discount_dod_diff, 0)) AS recurring_discount_dod_diff
  100. , SUM(COALESCE(nonrecurring_discount_prior_day, 0)) AS nonrecurring_discount_prior_day
  101. , SUM(COALESCE(nonrecurring_discount, 0)) AS nonrecurring_discount
  102. , SUM(COALESCE(nonrecurring_discount_dod_diff, 0)) AS nonrecurring_discount_dod_diff
  103. , SUM(COALESCE(net_mrr_prior_day, 0)) AS net_mrr_prior_day
  104. , SUM(COALESCE(net_mrr, 0)) AS net_mrr
  105. , SUM(COALESCE(net_mrr_dod_diff, 0)) AS net_mrr_dod_diff
  106. , SUM(COALESCE(baseplan_quantity_dod_diff, 0)) AS baseplan_quantity_dod_diff
  107. , SUM(COALESCE(addon_quantity_dod_diff, 0)) AS addon_quantity_dod_diff
  108. , STRING_AGG(DISTINCT dod_mrr_diff_type_transfer_adj, ', ') AS dod_mrr_diff_type_transfer_adj
  109. , STRING_AGG(DISTINCT sales_model, ', ') AS sales_model
  110. , STRING_AGG(DISTINCT salesamendmenttype__c, ', ') AS salesamendmenttype__c
  111. , STRING_AGG(DISTINCT base_product_plan_prior_day, ', ') AS base_product_plan_prior_day
  112. , STRING_AGG(DISTINCT base_product_plan, ', ') AS base_product_plan
  113. , STRING_AGG(DISTINCT product_addon, ', ') AS product_addon
  114. , STRING_AGG(DISTINCT CAST(currentterm_prior_day AS STRING), ', ') AS currentterm_prior_day
  115. , STRING_AGG(DISTINCT CAST(currentterm AS STRING), ', ') AS currentterm
  116. , STRING_AGG(DISTINCT billingperiod, ', ') AS billingperiod
  117. , STRING_AGG(DISTINCT crm_product_line_paid_combo_prior_day, ', ') AS crm_product_line_paid_combo_prior_day
  118. , STRING_AGG(DISTINCT crm_product_line_paid_combo, ', ') AS crm_product_line_paid_combo
  119. , STRING_AGG(DISTINCT productrateplancharge_id, ', ') AS productrateplancharge_id
  120. , STRING_AGG(DISTINCT crm_account_id, ', ') AS crm_account_id
  121. , STRING_AGG(DISTINCT zendesk_account_id, ', ') AS zendesk_account_id
  122. , STRING_AGG(DISTINCT zopim_account_id, ', ') AS zopim_account_id
  123. , STRING_AGG(DISTINCT masteraccountid__c, ', ') AS masteraccountid__c
  124. , STRING_AGG(DISTINCT currency, ', ') AS currency
  125. , STRING_AGG(DISTINCT initiatedby, ', ') AS initiatedby
  126. , STRING_AGG(DISTINCT paymentterm, ', ') AS paymentterm
  127. , COUNT(DISTINCT base_product_plan) AS base_product_plan_count
  128. , COUNT(DISTINCT currentterm) AS currentterm_count
  129. , COUNT(DISTINCT billingperiod) AS billingperiod_count
  130. , COUNT(DISTINCT productrateplancharge_id) AS productrateplancharge_count
  131. FROM cte0_unaggr
  132. WHERE
  133. (base_product_plan NOT IN ('Lite','Partner Edition')
  134. OR (base_product_plan IS NULL AND (base_product_plan_prior_day NOT IN ('Lite','Partner Edition')
  135. OR base_product_plan_prior_day IS NULL)))
  136. AND
  137. (base_product_plan_prior_day NOT IN ('Lite','Partner Edition')
  138. OR (base_product_plan_prior_day IS NULL AND (base_product_plan NOT IN ('Lite','Partner Edition')
  139. OR base_product_plan IS NULL)))
  140. AND NOT
  141. (product_line = 'Support'
  142. AND product_type_derived = 'Addon')
  143. GROUP BY 1,2,3,4,5
  144. )
  145. ,cte2a_aggr_BA_PL_PT_excl AS (
  146. SELECT DISTINCT
  147. mrr_date
  148. , billing_account_id
  149. , product_line
  150. , product_type_derived
  151. , suite_ind
  152. , SUM(COALESCE(gross_mrr_prior_day, 0)) AS gross_mrr_prior_day
  153. , SUM(COALESCE(gross_mrr, 0)) AS gross_mrr
  154. , SUM(COALESCE(gross_mrr_dod_diff, 0)) AS gross_mrr_dod_diff
  155. , SUM(COALESCE(recurring_discount_prior_day, 0)) AS recurring_discount_prior_day
  156. , SUM(COALESCE(recurring_discount, 0)) AS recurring_discount
  157. , SUM(COALESCE(recurring_discount_dod_diff, 0)) AS recurring_discount_dod_diff
  158. , SUM(COALESCE(nonrecurring_discount_prior_day, 0)) AS nonrecurring_discount_prior_day
  159. , SUM(COALESCE(nonrecurring_discount, 0)) AS nonrecurring_discount
  160. , SUM(COALESCE(nonrecurring_discount_dod_diff, 0)) AS nonrecurring_discount_dod_diff
  161. , SUM(COALESCE(net_mrr_prior_day, 0)) AS net_mrr_prior_day
  162. , SUM(COALESCE(net_mrr, 0)) AS net_mrr
  163. , SUM(COALESCE(net_mrr_dod_diff, 0)) AS net_mrr_dod_diff
  164. , SUM(COALESCE(baseplan_quantity_dod_diff, 0)) AS baseplan_quantity_dod_diff
  165. , SUM(COALESCE(addon_quantity_dod_diff, 0)) AS addon_quantity_dod_diff
  166. , STRING_AGG(DISTINCT dod_mrr_diff_type_transfer_adj, ', ') AS dod_mrr_diff_type_transfer_adj
  167. , STRING_AGG(DISTINCT sales_model, ', ') AS sales_model
  168. , STRING_AGG(DISTINCT salesamendmenttype__c, ', ') AS salesamendmenttype__c
  169. , STRING_AGG(DISTINCT base_product_plan_prior_day, ', ') AS base_product_plan_prior_day
  170. , STRING_AGG(DISTINCT base_product_plan, ', ') AS base_product_plan
  171. , STRING_AGG(DISTINCT product_addon, ', ') AS product_addon
  172. , STRING_AGG(DISTINCT CAST(currentterm_prior_day AS STRING), ', ') AS currentterm_prior_day
  173. , STRING_AGG(DISTINCT CAST(currentterm AS STRING), ', ') AS currentterm
  174. , STRING_AGG(DISTINCT billingperiod, ', ') AS billingperiod
  175. , STRING_AGG(DISTINCT crm_product_line_paid_combo_prior_day, ', ') AS crm_product_line_paid_combo_prior_day
  176. , STRING_AGG(DISTINCT crm_product_line_paid_combo, ', ') AS crm_product_line_paid_combo
  177. , STRING_AGG(DISTINCT productrateplancharge_id, ', ') AS productrateplancharge_id
  178. , STRING_AGG(DISTINCT crm_account_id, ', ') AS crm_account_id
  179. , STRING_AGG(DISTINCT zendesk_account_id, ', ') AS zendesk_account_id
  180. , STRING_AGG(DISTINCT zopim_account_id, ', ') AS zopim_account_id
  181. , STRING_AGG(DISTINCT masteraccountid__c, ', ') AS masteraccountid__c
  182. , STRING_AGG(DISTINCT currency, ', ') AS currency
  183. , STRING_AGG(DISTINCT initiatedby, ', ') AS initiatedby
  184. , STRING_AGG(DISTINCT paymentterm, ', ') AS paymentterm
  185. , COUNT(DISTINCT base_product_plan) AS base_product_plan_count
  186. , COUNT(DISTINCT currentterm) AS currentterm_count
  187. , COUNT(DISTINCT billingperiod) AS billingperiod_count
  188. , COUNT(DISTINCT productrateplancharge_id) AS productrateplancharge_count
  189. FROM cte0_unaggr
  190. WHERE
  191. (base_product_plan = 'Partner Edition'
  192. OR base_product_plan_prior_day = 'Partner Edition')
  193. OR
  194. (product_line = 'Support'
  195. AND product_type_derived = 'Addon')
  196. GROUP BY 1,2,3,4,5
  197. )
  198.  
  199. , cte1b_orig_rpc_prep AS (
  200. SELECT DISTINCT
  201. mrr_date
  202. , billing_account_id
  203. , product_line
  204. , product_type_derived
  205. , suite_ind
  206. , original_rpc_id
  207. , SUM(COALESCE(net_mrr_dod_diff, 0)) AS net_mrr_dod_diff
  208. , SUM(COALESCE(gross_mrr, 0)) AS gross_mrr
  209. FROM cte0_unaggr
  210. WHERE
  211. (base_product_plan NOT IN ('Lite','Partner Edition')
  212. OR (base_product_plan IS NULL AND (base_product_plan_prior_day NOT IN ('Lite','Partner Edition')
  213. OR base_product_plan_prior_day IS NULL)))
  214. AND
  215. (base_product_plan_prior_day NOT IN ('Lite','Partner Edition')
  216. OR (base_product_plan_prior_day IS NULL AND (base_product_plan NOT IN ('Lite','Partner Edition')
  217. OR base_product_plan IS NULL)))
  218. AND NOT
  219. (product_line = 'Support'
  220. AND product_type_derived = 'Addon')
  221. GROUP BY 1,2,3,4,5,6
  222. )
  223.  
  224.  
  225. , cte2b_orig_rpc_prep AS (
  226. SELECT DISTINCT
  227. mrr_date
  228. , billing_account_id
  229. , product_line
  230. , product_type_derived
  231. , suite_ind
  232. , original_rpc_id
  233. , SUM(COALESCE(net_mrr_dod_diff, 0)) AS net_mrr_dod_diff
  234. , SUM(COALESCE(gross_mrr, 0)) AS gross_mrr
  235. FROM cte0_unaggr
  236. WHERE
  237. (base_product_plan = 'Partner Edition'
  238. OR base_product_plan_prior_day = 'Partner Edition')
  239. OR
  240. (product_line = 'Support'
  241. AND product_type_derived = 'Addon')
  242. GROUP BY 1,2,3,4,5,6
  243. )
  244. , cte1c_orig_rpc_select AS (
  245. SELECT DISTINCT
  246. mrr_date
  247. , billing_account_id
  248. , product_line
  249. , product_type_derived
  250. , suite_ind
  251. , FIRST_VALUE(original_rpc_id)
  252. OVER (PARTITION BY mrr_date
  253. , billing_account_id
  254. , product_line
  255. , product_type_derived
  256. , suite_ind
  257. ORDER BY mrr_date
  258. , billing_account_id
  259. , product_line
  260. , product_type_derived
  261. , suite_ind
  262. , ABS(net_mrr_dod_diff) DESC
  263. , gross_mrr DESC
  264. , original_rpc_id)
  265. AS original_rpc_id
  266. FROM cte1b_orig_rpc_prep
  267. )
  268.  
  269. , cte2c_orig_rpc_select AS (
  270. SELECT DISTINCT
  271. mrr_date
  272. , billing_account_id
  273. , product_line
  274. , product_type_derived
  275. , suite_ind
  276. , FIRST_VALUE(original_rpc_id)
  277. OVER (PARTITION BY mrr_date
  278. , billing_account_id
  279. , product_line
  280. , product_type_derived
  281. , suite_ind
  282. ORDER BY mrr_date
  283. , billing_account_id
  284. , product_line
  285. , product_type_derived
  286. , suite_ind
  287. , ABS(net_mrr_dod_diff) DESC
  288. , gross_mrr DESC
  289. , original_rpc_id)
  290. AS original_rpc_id
  291. FROM cte2b_orig_rpc_prep
  292. )
  293.  
  294.  
  295. ,cte1_join_main as
  296. (select
  297. a.*,
  298. c.original_rpc_id
  299. from cte1a_aggr_BA_PL_PT_main as a
  300. left join cte1c_orig_rpc_select as c on
  301. (a.mrr_date=c.mrr_date
  302. and a.billing_account_id=c.billing_account_id
  303. and a.product_line=c.product_line
  304. and a.product_type_derived=c.product_type_derived
  305. and a.suite_ind=c.suite_ind)
  306. )
  307. ,cte2_join_excl as
  308. (select
  309. a.*,
  310. c.original_rpc_id
  311. from cte2a_aggr_BA_PL_PT_excl as a
  312. left join cte2c_orig_rpc_select as c on
  313. (a.mrr_date=c.mrr_date
  314. and a.billing_account_id=c.billing_account_id
  315. and a.product_line=c.product_line
  316. and a.product_type_derived=c.product_type_derived
  317. and a.suite_ind=c.suite_ind)
  318. )
  319. ,cte3_union as (
  320. select * from cte2_join_excl
  321. union all
  322. select * from cte1_join_main)
  323.  
  324. , cte3 as
  325. (SELECT DISTINCT
  326. j.mrr_date
  327. , j.billing_account_id
  328. , j.product_line
  329. , j.product_type_derived
  330. , j.suite_ind
  331. , ROUND(j.gross_mrr_prior_day,2) AS gross_mrr_prior_day
  332. , ROUND(j.recurring_discount_prior_day,2) AS recurring_discount_prior_day
  333. , ROUND(j.nonrecurring_discount_prior_day,2) AS nonrecurring_discount_prior_day
  334. , ROUND(j.net_mrr_prior_day,2) AS net_mrr_prior_day
  335. , ROUND(j.gross_mrr,2) AS gross_mrr
  336. , ROUND(j.recurring_discount,2) AS recurring_discount
  337. , ROUND(j.nonrecurring_discount,2) AS nonrecurring_discount
  338. , ROUND(j.net_mrr,2) AS net_mrr
  339. , ROUND(j.gross_mrr_dod_diff,2) AS gross_mrr_dod_diff
  340. , ROUND(j.recurring_discount_dod_diff,2) AS recurring_discount_dod_diff
  341. , ROUND(j.nonrecurring_discount_dod_diff,2) AS nonrecurring_discount_dod_diff
  342. , ROUND(j.net_mrr_dod_diff,2) AS net_mrr_dod_diff
  343. , CASE WHEN ROUND(j.net_mrr_dod_diff,2) > 0
  344. AND ROUND(j.recurring_discount_dod_diff,2)
  345. + ROUND(j.nonrecurring_discount_dod_diff,2) > 0
  346. THEN ROUND(j.gross_mrr_dod_diff,2)
  347. ELSE ROUND(j.net_mrr_dod_diff,2) END
  348. AS mrr_dod_diff_adj
  349. , j.baseplan_quantity_dod_diff
  350. , j.addon_quantity_dod_diff
  351. , j.sales_model
  352. , j.salesamendmenttype__c
  353. , j.dod_mrr_diff_type_transfer_adj
  354. , j.currentterm_prior_day
  355. , j.currentterm AS currentterm_current_day
  356. , CASE WHEN j.dod_mrr_diff_type_transfer_adj = 'Customer Churn' THEN ''
  357. ELSE COALESCE((CASE WHEN j.currentterm_count = 1
  358. THEN j.currentterm
  359. ELSE CAST(u.currentterm AS STRING) END)
  360. , j.currentterm_prior_day
  361. , CAST(u.currentterm_prior_day AS STRING))
  362. END AS currentterm
  363. , j.billingperiod AS billingperiod_list
  364. , CASE WHEN j.billingperiod_count = 1
  365. THEN j.billingperiod
  366. ELSE u.billingperiod
  367. END AS billingperiod
  368. , j.base_product_plan_prior_day
  369. , j.base_product_plan AS base_product_plan_current_day
  370. , COALESCE(j.base_product_plan, j.base_product_plan_prior_day) AS base_product_plan
  371. , j.product_addon
  372. , CASE WHEN j.productrateplancharge_count = 1
  373. THEN j.productrateplancharge_id
  374. ELSE u.productrateplancharge_id
  375. END AS productrateplancharge_id
  376. , j.crm_account_id
  377. , j.masteraccountid__c
  378. , j.currency
  379. , j.initiatedby
  380. , j.paymentterm
  381. , j.base_product_plan_count
  382. , j.currentterm_count
  383. , j.billingperiod_count
  384. , j.productrateplancharge_count
  385. , j.zendesk_account_id
  386. , j.zopim_account_id
  387. , j.crm_product_line_paid_combo_prior_day
  388. , j.crm_product_line_paid_combo
  389. from cte3_union as j
  390. left join cte0_unaggr as u on
  391. (j.mrr_date=u.mrr_date and j.original_rpc_id=u.original_rpc_id)
  392. ), edw AS (
  393.  
  394. SELECT DISTINCT
  395. CASE WHEN cte3.mrr_dod_diff_adj = 0
  396. THEN 'EXCLUDE: MRR change = 0'
  397. WHEN cte3.dod_mrr_diff_type_transfer_adj = 'Customer No Change'
  398. THEN 'EXCLUDE: Customer No Change'
  399. WHEN cte3.crm_account_id IS NULL
  400. AND cte3.gross_mrr_prior_day = 0
  401. THEN 'New Business'
  402. WHEN cte3.dod_mrr_diff_type_transfer_adj = 'Customer New'
  403. AND cte3.gross_mrr_prior_day = 0
  404. THEN 'New Business'
  405. WHEN cte3.dod_mrr_diff_type_transfer_adj = 'Customer Return'
  406. AND cte3.gross_mrr_prior_day = 0
  407. THEN 'Return'
  408. WHEN cte3.dod_mrr_diff_type_transfer_adj = 'Customer Churn'
  409. THEN 'Churn'
  410. WHEN cte3.mrr_dod_diff_adj > 0
  411. AND cte3.baseplan_quantity_dod_diff > 0
  412. AND cte3.gross_mrr_prior_day > 0
  413. THEN 'Agent Adds'
  414. WHEN cte3.base_product_plan != cte3.base_product_plan_prior_day
  415. AND cte3.crm_product_line_paid_combo_prior_day LIKE CONCAT('%',cte3.product_line,'%')
  416. AND cte3.crm_product_line_paid_combo LIKE CONCAT('%',cte3.product_line,'%')
  417. THEN 'Plan Shift'
  418. WHEN cte3.mrr_dod_diff_adj > 0
  419. AND cte3.gross_mrr_prior_day = 0
  420. THEN 'Add Product'
  421. WHEN cte3.mrr_dod_diff_adj < 0
  422. AND cte3.baseplan_quantity_dod_diff < 0
  423. THEN 'Agent Contraction'
  424. WHEN cte3.currentterm != cte3.currentterm_prior_day
  425. OR cte3.billingperiod_count > 1
  426. THEN 'New Contract Term'
  427. WHEN cte3.gross_mrr_dod_diff > 0
  428. AND cte3.mrr_dod_diff_adj > 0
  429. AND cte3.baseplan_quantity_dod_diff = 0
  430. AND cte3.billingperiod_count = 1
  431. AND cte3.currentterm = cte3.currentterm_prior_day
  432. THEN 'New Contract Term'
  433. WHEN cte3.gross_mrr_dod_diff = 0
  434. AND cte3.mrr_dod_diff_adj < 0
  435. THEN 'New Contract Term'
  436. END AS changetype
  437. , CASE WHEN billingperiod IN ('Month', 'Monthly') THEN 'Monthly'
  438. WHEN billingperiod IN ('Quarter', 'Quarterly') THEN 'Quarterly'
  439. WHEN billingperiod IN ('Semi-Annual', 'Semiannually', 'Semi-Annually') THEN 'Semi-Annually'
  440. WHEN billingperiod IN ('Annual', 'Annually') THEN 'Annually'
  441. WHEN billingperiod IN ('2 Year', 'Two Years', '2 years') THEN '2 years'
  442. WHEN billingperiod IN ('3 Year', '3 years') THEN '3 years'
  443. ELSE billingperiod END AS billingperiod
  444. , cte3.* EXCEPT(billingperiod)
  445. , current_timestamp as dw_created_timestamp
  446. , billing_account_type_clean
  447. FROM cte3
  448. LEFT JOIN `edw-prod-153420.SourceData.customer_dim_scd2` cd
  449. ON cte3.billing_account_id = cd.billing_account_id
  450. AND dw_curr_ind = 'Y'
  451. )
  452.  
  453. select
  454. edw.changetype
  455. , mrr_date
  456. , billing_account_id
  457. , product_line
  458. , product_type_derived
  459. , mrr_dod_diff_adj
  460. , sales_model
  461. , currentterm
  462. , billingperiod
  463. , base_product_plan
  464. , zendesk_account_id
  465. , crm_account_id
  466. , zopim_account_id
  467. , Concat(cast(format_date('%Y',mrr_date) as string),"-"
  468. ,cast(replace(format_date('%m',mrr_date),'0','') as string),"-"
  469. ,cast(format_date('%d',mrr_date) as string),"-"
  470. ,Billing_Account_ID,"-"
  471. ,ProductRatePlanCharge_ID,"-"
  472. ,Product_Line) as compositekey, sod.Composite_Key__c
  473. , sod.Opportunity_ID__c
  474. , billing_account_type_clean
  475. from edw
  476. left join `edw-prod-153420.SourceData.sfdc_staging_order_data_scd2` as sod
  477. on (sod.Composite_Key__c=Concat(cast(format_date('%Y',mrr_date) as string),"-"
  478. ,cast(replace(format_date('%m',mrr_date),'0','') as string),"-"
  479. ,cast(format_date('%d',mrr_date) as string),"-"
  480. ,Billing_Account_ID,"-"
  481. ,ProductRatePlanCharge_ID,"-"
  482. ,Product_Line) and sod.dw_curr_ind="Y")
  483. where changetype not like 'EXCLUDE%'
  484. order by mrr_date
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!