Queries

Dawn Foster edited this page Jun 4, 2015 · 5 revisions

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='example@example.com' 
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.
Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.