1

I'm having issues with a pretty complicated postgreSQL query that I can briefly sum up as follows:

I have a set of costumers who buy apples, oranges, or both. I also know each date of each purchase. What I'd like is the date of the first purchase for each customer and each fruit.

To do this, I use the following query:

SELECT distinct 
CASE WHEN fruitType = 'apple' then min(purchaseDate)
ELSE null END AS appleFirstPurchaseDate
, CASE WHEN fruitType = 'orange' then min(purchaseDate)
ELSE null END AS orangeFirstPurchaseDate
FROM fruitPurchases
GROUP BY fruitType

I also have this version (I don't know which one is better for this problem):

SELECT distinct 
CASE WHEN fruitType = 'apple' then min(purchaseDate) over(partition by fruitType) 
ELSE null END AS appleFirstPurchaseDate
, CASE WHEN fruitType = 'orange' then min(purchaseDate) over(partition by fruitType)
ELSE null END AS orangeFirstPurchaseDate
FROM fruitPurchases

Both give the same result and, when the customer buys only oranges or only apples, it works fine. However, when they've already bought both, I get this for one same customer:

appleFirstPurchaseDate  orangeFirstPurchaseDate
----------------------  -----------------------
       2017-05-03                [NULL]
         [NULL]                2016-11-25

While the result I'm dreaming of is:

appleFirstPurchaseDate  orangeFirstPurchaseDate
----------------------  -----------------------
       2017-05-03              2016-11-25

I also checked this solution (the first of the 3 options) that suggests the use of the MAX function but, because of my MIN function, I get this error message: "aggregate functions calls cannot be nested".

I am vaguely aware that a subquery could solve this problem but, given the whole messy code that I'm working on, I'd rather get a solution that doesn't add new FROM clauses and that would keep using CASE WHEN's. If impossible, I'd be happy with any solution :)

Guillaume
  • 168
  • 2
  • 14

1 Answers1

2
select
    min(purchaseDate) filter (where fruitType = 'apple') as apple,
    min(purchaseDate) filter (where fruitType = 'orange') as orange,
    customer
from fruitPurchases
group by customer
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Yeeeaaah it now works fine :) I didn't know this FILTER function but I'll definitely keep using it! Thank you! – Guillaume Nov 14 '17 at 17:22