I have this data:
process_date phrase code value
------------ ------ ---- -----
2018-12-21 ball A 5
2018-12-21 ball B 10
2018-12-21 ball C 5
2018-12-20 ball A 5
2018-12-20 ball B 15
2018-12-19 ball A 20
2018-12-19 ball C 5
2018-12-18 ball A 5
2018-12-18 ball B 20
2018-12-17 ball A 15
2018-12-17 ball B 10
2018-12-17 ball C 5
2018-12-21 car A 15
2018-12-21 car C 5
2018-12-20 car A 5
2018-12-20 car A 20
2018-12-20 car C 10
2018-12-19 car A 5
2018-12-18 car A 10
2018-12-18 car B 15
2018-12-17 car A 20
2018-12-17 car B 5
2018-12-21 bicycle A 10
2018-12-21 bicycle B 15
2018-12-20 bicycle A 5
2018-12-18 bicycle A 15
2018-12-18 bicycle B 10
2018-12-17 bicycle C 10
INFO:
- there will be much more dates going back in the past for each day, but for simplicity of the example I've included only 5 days of data and only 3 phrases
- There will never be a row with same date, phrase and code (unique key)
REQUEST:
I need to create a query that returns me the counts and sums for phrases groups that fulfill these conditions:
- only include in the calculations the 3 most recent dates for which there is data in this table (in the example 2018-12-21, 2018-12-20 and 2018-12-19)
- only those phrases that have had at least in 2 of the dates a row with value "A" and at least in 1 of the dates a row with code "B" (so "car" would not fulfil this condition because eventhough there was "A" in 2 of the last 3 dates, there was no row with value "B" in any of those 3 last dates)
IMPORTANT: Notice that in the example, there was no row for "bicycle" in 2018-12-19 , but that should not avoid this phrase from fulfilling the conditions
EXPECTED RETURN FROM QUERY:
phrase A_count A_sum B_count B_sum C_count C_sum
------- ------- ----- ------- ----- ------- -----
ball 3 30 2 25 2 10
bicycle 2 15 1 15 0 0
Thank you!
