Queries

Top 100 message senders

SELECT COUNT(DISTINCT(messages.message_ID)) AS total, messages_people.email_address
FROM messages, messages_people
WHERE messages.message_ID = messages_people.message_id
AND messages_people.type_of_recipient = 'From'
GROUP BY messages_people.email_address
ORDER BY total DESC
LIMIT 100

Top 100 message senders during 2013

SELECT COUNT(DISTINCT(messages.message_ID)) AS total, messages_people.email_address
FROM messages, messages_people
WHERE messages.message_ID = messages_people.message_id
AND messages_people.type_of_recipient = 'From'
AND YEAR(messages.first_date) = 2013
GROUP BY messages_people.email_address
ORDER BY total DESC
LIMIT 100

Top 100 messages of all time (most replied to threads)

SELECT subject, YEAR(first_date), COUNT(*) as total
FROM messages 
GROUP BY subject, YEAR(first_date) 
ORDER by total DESC
LIMIT 100

Total number of messages

SELECT COUNT(*) FROM messages;

Total number of messages per month from a specific person

SELECT messages_people.email_address, YEAR(messages.first_date), MONTHNAME(messages.first_date), COUNT(*) 
FROM messages, messages_people 
WHERE messages_people.email_address='[email protected]' 
AND messages.message_id=messages_people.message_id 
GROUP BY YEAR(messages.first_date), MONTH(messages.first_date);

Additional Resources

• Dave Neary also wrote some more advanced queries - note that these are a bit older and might need to be updated.