Advertisement
Guest User

Untitled

a guest
Jun 20th, 2019
67
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.40 KB | None | 0 0
  1. ## Table 1,2
  2. with channel as (
  3. select
  4. date_format(cdate, '%Y-%m-01') as cmonth,
  5. channel,
  6. count(distinct customer) as noCustomer,
  7. count(distinct order_id) as noOrder,
  8. sum(amount) as NMV
  9.  
  10. from cohort_analysis
  11. where
  12.     cdate >= '2019-02-01'
  13. group by 1,2
  14. ),
  15.  
  16. total as (
  17. select
  18. date_format(cdate, '%Y-%m-01') as cmonth,
  19. 'all',
  20. count(distinct customer) as noCustomer,
  21. count(distinct order_id) as noOrder,
  22. sum(amount) as NMV
  23.  
  24. from cohort_analysis
  25. where
  26.     cdate >= '2019-02-01'
  27. group by 1,2
  28. ),
  29.  
  30. c as (
  31. select
  32. customer,
  33. date_format(cdate, '%Y-%m-01') as cmonth,
  34. group_concat(distinct channel) as ls_channel
  35. from cohort_analysis
  36. where
  37.     cdate >= '2019-02-01'
  38.  
  39. group by 1,2
  40. ),
  41.  
  42. omni_customer as (
  43. select
  44. *
  45. from c
  46. where ls_channel <> 'offline'
  47. and ls_channel <> 'online'
  48. ),
  49.  
  50. omni as (
  51. select
  52. date_format(cohort_analysis.cdate, '%Y-%m-01') as cmonth,
  53. 'omni' as channel,
  54. count(distinct cohort_analysis.customer) as noCustomer,
  55. count(distinct cohort_analysis.order_id) as noOrder,
  56. sum(cohort_analysis.amount) as NMV
  57.  
  58. from
  59.     cohort_analysis
  60.  
  61. left join
  62.     omni_customer
  63.     on omni_customer.customer = cohort_analysis.customer
  64.     and date_format(cohort_analysis.cdate, '%Y-%m-01') = omni_customer.cmonth
  65.  
  66. where
  67.     cohort_analysis.cdate >= '2019-02-01'
  68.     and omni_customer.ls_channel is not null
  69. group by 1,2
  70. )
  71.  
  72. select * from channel union all select * from total union all select * from omni
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement