How to add field from a single row to aggregate of rows presto sql

Question

I have a presto query to return the total count of fruit eaten by a particular person. I would also like to include the most recent time that the fruit was eaten.

Here is my query:

SELECT 
  id,
  fruittype,
  count(*) as fruitconsumptioncount
FROM
  (VALUES
    ( 'John', 'apple', '2017-10-15 16:35:27.000'),
    ( 'John', 'apple', '2017-10-16 16:35:27.000'),
    ( 'John', 'apple', '2017-10-17 16:35:27.000'),
    ( 'John', 'orange', '2017-10-14 16:35:27.000'),
    ( 'John', 'orange', '2017-10-15 16:35:27.000'),
    ( 'John', 'orange', '2017-10-20 16:35:27.000'),
    ( 'John', 'banana', '2017-10-18 16:35:27.000'),
    ( 'Bob', 'kiwi', '2017-10-15 16:35:27.000')
  ) as dimensions (id, fruittype, consumptiontime) 
GROUP BY
  id,
  fruittype
ORDER BY
  id,
  fruitconsumptioncount DESC`

This returns:

id     fruittype    fruitconsumptioncount
Bob     kiwi         1
John    apple        3
John    orange       3
John    banana       1

I need it to return:

id     fruittype    fruitconsumptioncount mostrecentconsumption
Bob     kiwi         1                     2017-10-15 16:35:27.000
John    apple        3                     2017-10-17 16:35:27.000
John    orange       3                     2017-10-20 16:35:27.000
John    banana       1                     2017-10-18 16:35:27.000

This is because I want to eventually sort for top fruittype per person, but in the case of a tie, I want to return the tied fruit that was most recently eaten.

Gholamali Irani · Accepted Answer · 2017-12-11T22:06:03.693

Assume that your Table is A:

A:
        id    fruittype   consumptiontime
    ----------------------------------------------- 
    ( 'John', 'apple', '2017-10-15 16:35:27.000'),
    ( 'John', 'apple', '2017-10-16 16:35:27.000'),
    ( 'John', 'apple', '2017-10-17 16:35:27.000'),
    ( 'John', 'orange', '2017-10-14 16:35:27.000'),
    ( 'John', 'orange', '2017-10-15 16:35:27.000'),
    ( 'John', 'orange', '2017-10-20 16:35:27.000'),
    ( 'John', 'banana', '2017-10-18 16:35:27.000'),
    ( 'Bob', 'kiwi', '2017-10-15 16:35:27.000')

So, If consumptiontime is a DateTime data type or something like that, Try this:

SELECT id, fruittype, count(*) as fruitconsumptioncount, max(consumptiontime)
FROM A  
GROUP BY id, fruittype 
ORDER BY id, fruitconsumptioncount DESC

And if it is string, just convert to date.
See: Presto SQL - Converting a date string to date format

How to add field from a single row to aggregate of rows presto sql

1 Answers1