sql - Divide column value by number of rows within a date range -
i have table in impala fields: campaign id, account, start date, end date, transaction date , revenue. there multiple campaigns having same account , revenue values. want divide revenue values between campaigns fall within range [transaction_date, transaction_date + 36 months].
sample table:
campaign | account | start date | end date | trans. date | revenue 1 | 1234 | 13-05-17 | 13-06-17 | 19-10-17 | 200 2 | 1234 | 14-01-16 | 14-02-16 | 19-10-17 | 200 2 | 5678 | 14-01-16 | 14-02-16 | 07-02-16 | 200 3 | 2345 | 20-05-15 | 20-07-15 | 22-05-15 | 300 4 | 1234 | 15-10-13 | 15-11-13 | 19-10-17 | 200 4 | 5678 | 15-10-13 | 15-11-13 | 22-05-15 | 300
here, revenue account 1234 should split between campaigns 1 , 2, , not 4 since transaction date falls 36 months after campaign started. while revenue account 2345 should split between campaigns 2 , 4.
result table should as:
campaign | account | start date | end date | trans. date | revenue | avg revenue 1 | 1234 | 13-05-17 | 13-06-17 | 19-10-17 | 200 | 100 2 | 1234 | 14-01-16 | 14-02-16 | 19-10-17 | 200 | 100 2 | 5678 | 14-01-16 | 14-02-16 | 07-02-16 | 200 | 200 3 | 2345 | 20-05-15 | 20-07-15 | 22-05-15 | 300 | 150 4 | 1234 | 15-10-13 | 15-11-13 | 19-10-17 | 200 | null 4 | 2345 | 15-10-13 | 15-11-13 | 22-05-15 | 300 | 150
edit:
essentially, want following:
1. each row, rows account trans_date falls between start date , start date + 3 years.
2. divide revenue in each of rows number of rows.
have tried make work using partition, not sure how create 1 variable range based on date values.
hope makes clearer.
thanks!
this work in oracle, concept should able adapted postgres..
drop table test; create table test select 1 campaign, 1234 account, to_date('13-05-17', 'dd-mm-yy') start_date, to_date('13-06-17', 'dd-mm-yy') end_date, to_date('19-10-17', 'dd-mm-yy') trans_date, 200 revenue dual union select 2 campaign, 1234 account, to_date('14-01-16', 'dd-mm-yy') start_date, to_date('14-02-16', 'dd-mm-yy') end_date, to_date('19-10-17', 'dd-mm-yy') trans_date, 200 revenue dual union select 2 campaign, 5678 account, to_date('14-01-16', 'dd-mm-yy') start_date, to_date('14-02-16', 'dd-mm-yy') end_date, to_date('07-02-16', 'dd-mm-yy') trans_date, 200 revenue dual union select 3 campaign, 2345 account, to_date('20-05-15', 'dd-mm-yy') start_date, to_date('20-07-15', 'dd-mm-yy') end_date, to_date('22-05-15', 'dd-mm-yy') trans_date, 300 revenue dual union select 4 campaign, 1234 account, to_date('15-10-13', 'dd-mm-yy') start_date, to_date('15-11-13', 'dd-mm-yy') end_date, to_date('19-10-17', 'dd-mm-yy') trans_date, 200 revenue dual union select 4 campaign, 2345 account, to_date('15-10-13', 'dd-mm-yy') start_date, to_date('15-11-13', 'dd-mm-yy') end_date, to_date('22-05-15', 'dd-mm-yy') trans_date, 300 revenue dual ; select a.* ,case when start_date + (365 * 3) > trans_date revenue else null end / count(case when start_date + (365 * 3) > trans_date 1 else null end) on (partition account) avg_revenue test order campaign, account
Comments
Post a Comment