sample_user_queries
Sample SQL queries
See the database schema documentation for definitions of tables and columns.
Messages with a private note attached
SELECT mail_id FROM notes
The last 20 messages that have a private note
SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20
Messages with a private note which has been added or modified during the last 7 days
SELECT mail_id FROM notes WHERE last_changed>=now()-'7 days'::INTERVAL
Messages with one or more pictures attached
SELECT mail_id FROM attachments WHERE content_type LIKE 'image/%'
Outgoing messages that have been composed less than one month ago
SELECT m.mail_id FROM mail m WHERE msg_date>=now()-'1 month'::INTERVAL AND status&256=256
Messages with many recipients in To field
(example: more than 20 recipients)
SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id HAVING COUNT(*)>20
Messages with many recipients in To/Cc field
(example: more than 20)
SELECT mail_id FROM mail_addresses WHERE addr_type IN (2,3) GROUP BY mail_id HAVING COUNT(*)>20
Messages with any sender or recipient from a given domain
SELECT a.mail_id FROM mail_addresses a JOIN addresses USING(addr_id) WHERE email_addr LIKE '%@example.com'
Messages with attachments bigger than a given size
(example: bigger than 1Mb)
SELECT mail_id FROM attachments WHERE content_size > 1024*1024
See the database schema documentation for definitions of tables and columns.
sample_user_queries.txt · Last modified: 2014/08/04 11:23 by daniel