Following setup:

* Message 1..N <-> 1 Topic
* Message 0..N <-> 1 Mood

Tables:

```
Message
| id
| text
| mood_id
| topic_id

Topic
| id
| title

Mood
| id
| title

```

Messages have to be posted to a particular topic. When posting a message to a topic, the user can attach his mood how he feels when writing that message to the message itself. 

I am trying to write a query which does the following:

* Aggregate the amount of messages for each topic
* Find the most used mood for each topic

The optimal output would be a list of something like this:

```
| topic.id
| topic.title
| most_used_mood_id
| message_count

```

I've been banging my head at this query for a good amount of time now without much progress. Counting the amount of messages is no problem, but calculating the most used emotion across all messages to a certain topic turned out to be quite tricky. 

Leads into the correct direction are greatly appreciated.