Skip to content
Advertisement

Complex SQL query aggregation and grouping on athena

I have a table like this:

I would like to retrieve the number of chat performed by users for each database (db) and the last part where I fail, retrieve also a list of all mentors by users.

The final output should be like this for example (notice there is only one time max for greg in the admin column)

I wrote the following query but it doesn’t aggregate the admins and i have separated nb_of chats/mentors.

As expected I am getting the following result (but I only want it on one line by db/user with grouped admin in the same column):

Have you an idea how to perform it ?

Thank you for your time !

Regards.

Advertisement

Answer

Firsly, remove adminsfrom the group by clause, since you want to aggregate it. Then, in Presto, you can do string aggregation as follows:

You can add an order by clause to array_agg() if needed:

Note that I changed count(chat_id) to count(*): both are equivalent (since chat_id probably is a non-nullable column), and the former is (sligthly) more efficient, and makes the intent clearer in my opinion.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement