Guest User

Untitled

a guest
Jun 19th, 2018
75
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.99 KB | None | 0 0
  1. SELECT
  2. customer_id,
  3. sum(order_total),
  4. referral_code
  5. FROM order
  6. GROUP BY customer_id
  7. ORDER BY date_created
  8.  
  9. select customer_id, sum(order_total)
  10. from order
  11. group by customer_id
  12.  
  13. select distinct on (customer_id) customer_id, date_created, referral_code
  14. from order
  15. order by customer_id, date_created
  16.  
  17. select
  18. x1.customer_id,
  19. x1.sum,
  20. x2.date_created,
  21. x2.referral_code
  22. from
  23. (
  24. select customer_id, sum(order_total)
  25. from order
  26. group by customer_id
  27. ) as x1
  28. join
  29. (
  30. select distinct on (customer_id) customer_id, date_Created, referral_code
  31. from order
  32. order by customer_id, date_created
  33. ) as x2 using ( customer_id )
  34. order by x2.date_created
  35.  
  36. create table ordertable (customer_id int, order_total int, referral_code char, date_created datetime)
  37. insert ordertable values (1,10, 'a', '2009-01-01')
  38. insert ordertable values (2,15, 'b', '2009-01-02')
  39. insert ordertable values (1,35, 'c', '2009-01-03')
  40.  
  41. SELECT
  42. orderAgg.customer_id,
  43. orderAgg.order_sum,
  44. referral.referral_code as first_referral_code
  45. FROM (
  46. SELECT
  47. customer_id,
  48. sum(order_total) as order_sum
  49. FROM ordertable
  50. GROUP BY customer_id
  51. ) as orderAgg join (
  52. SELECT
  53. customer_id,
  54. min(date_created) as first_date
  55. FROM ordertable
  56. GROUP BY customer_id
  57. ) as dateAgg on orderAgg.customer_id = dateAgg.customer_id
  58. join ordertable as referral
  59. on dateAgg.customer_id = referral.customer_id
  60. and dateAgg.first_date = referral.date_created
  61.  
  62. SELECT
  63. O1.customer_id,
  64. O1.referral_code,
  65. SQ.total
  66. FROM
  67. Orders O1
  68. LEFT OUTER JOIN Orders O2 ON
  69. O2.customer_id = O1.customer_id AND
  70. O2.date_created < O1.date_created
  71. INNER JOIN (
  72. SELECT
  73. customer_id,
  74. SUM(order_total) AS total
  75. FROM
  76. Orders
  77. GROUP BY
  78. customer_id
  79. ) SQ ON SQ.customer_id = O1.customer_id
  80. WHERE
  81. O2.customer_id IS NULL
  82.  
  83. SELECT
  84. customer_id,
  85. sum(order_total),
  86. (SELECT referral_code
  87. FROM order o
  88. WHERE o.customer_id = order.customer_id
  89. ORDER BY date_created
  90. LIMIT 1) AS customers_referral_code
  91. FROM order
  92. GROUP BY customer_id, customers_referral_code
  93. ORDER BY date_created
  94.  
  95. SELECT
  96. o.customer_id,
  97. sum(o.order_total),
  98. c.referral_code, c.x, c.y, c.z
  99. FROM order o LEFT JOIN (
  100. SELECT referral_code, x, y, z
  101. FROM orders c
  102. WHERE c.customer_id = o.customer_id
  103. ORDER BY c.date_created
  104. LIMIT 1
  105. ) AS c
  106. GROUP BY o.customer_id, c.referral_code
  107. ORDER BY o.date_created
  108.  
  109. SELECT customer_id, order_sum,
  110. (first_record).referral, (first_record).other_column
  111. FROM (
  112. SELECT customer_id,
  113. SUM(order_total) AS order_sum,
  114. (
  115. SELECT oi
  116. FROM order oi
  117. WHERE oi.customer_id = o.customer_id
  118. LIMIT 1
  119. ) AS first_record
  120. FROM order o
  121. GROUP BY
  122. customer_id
  123. ) q
Add Comment
Please, Sign In to add comment