Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- customer_id,
- sum(order_total),
- referral_code
- FROM order
- GROUP BY customer_id
- ORDER BY date_created
- select customer_id, sum(order_total)
- from order
- group by customer_id
- select distinct on (customer_id) customer_id, date_created, referral_code
- from order
- order by customer_id, date_created
- select
- x1.customer_id,
- x1.sum,
- x2.date_created,
- x2.referral_code
- from
- (
- select customer_id, sum(order_total)
- from order
- group by customer_id
- ) as x1
- join
- (
- select distinct on (customer_id) customer_id, date_Created, referral_code
- from order
- order by customer_id, date_created
- ) as x2 using ( customer_id )
- order by x2.date_created
- create table ordertable (customer_id int, order_total int, referral_code char, date_created datetime)
- insert ordertable values (1,10, 'a', '2009-01-01')
- insert ordertable values (2,15, 'b', '2009-01-02')
- insert ordertable values (1,35, 'c', '2009-01-03')
- SELECT
- orderAgg.customer_id,
- orderAgg.order_sum,
- referral.referral_code as first_referral_code
- FROM (
- SELECT
- customer_id,
- sum(order_total) as order_sum
- FROM ordertable
- GROUP BY customer_id
- ) as orderAgg join (
- SELECT
- customer_id,
- min(date_created) as first_date
- FROM ordertable
- GROUP BY customer_id
- ) as dateAgg on orderAgg.customer_id = dateAgg.customer_id
- join ordertable as referral
- on dateAgg.customer_id = referral.customer_id
- and dateAgg.first_date = referral.date_created
- SELECT
- O1.customer_id,
- O1.referral_code,
- SQ.total
- FROM
- Orders O1
- LEFT OUTER JOIN Orders O2 ON
- O2.customer_id = O1.customer_id AND
- O2.date_created < O1.date_created
- INNER JOIN (
- SELECT
- customer_id,
- SUM(order_total) AS total
- FROM
- Orders
- GROUP BY
- customer_id
- ) SQ ON SQ.customer_id = O1.customer_id
- WHERE
- O2.customer_id IS NULL
- SELECT
- customer_id,
- sum(order_total),
- (SELECT referral_code
- FROM order o
- WHERE o.customer_id = order.customer_id
- ORDER BY date_created
- LIMIT 1) AS customers_referral_code
- FROM order
- GROUP BY customer_id, customers_referral_code
- ORDER BY date_created
- SELECT
- o.customer_id,
- sum(o.order_total),
- c.referral_code, c.x, c.y, c.z
- FROM order o LEFT JOIN (
- SELECT referral_code, x, y, z
- FROM orders c
- WHERE c.customer_id = o.customer_id
- ORDER BY c.date_created
- LIMIT 1
- ) AS c
- GROUP BY o.customer_id, c.referral_code
- ORDER BY o.date_created
- SELECT customer_id, order_sum,
- (first_record).referral, (first_record).other_column
- FROM (
- SELECT customer_id,
- SUM(order_total) AS order_sum,
- (
- SELECT oi
- FROM order oi
- WHERE oi.customer_id = o.customer_id
- LIMIT 1
- ) AS first_record
- FROM order o
- GROUP BY
- customer_id
- ) q
Add Comment
Please, Sign In to add comment