Important SQL queries

Missing applications emails

Emails from users that have registered but have not completed the application.

SELECT u.email
FROM user_user u
WHERE NOT is_director AND NOT is_volunteer AND NOT is_organizer
AND u.id NOT IN 
(SELECT a.user_id FROM applications_application a);

MLH CSV files

Pre event

Generates file /tmp/confirmed.csv with all the fields required to submit to MLH before the event. (updated in March 2019)

COPY (
SELECT u.name, u.email, a.phone_number, a.university 
FROM applications_application a INNER JOIN user_user u ON u.id = a.user_id
WHERE a.status='C'
) TO '/tmp/confirmed.csv' WITH (FORMAT CSV, HEADER);

Post event

Generates file /tmp/attended.csv with all the fields required to submit to MLH after the event. (updated in March 2019)

COPY (
SELECT u.name, u.email, a.phone_number, a.university 
FROM applications_application a INNER JOIN user_user u ON u.id = a.user_id
WHERE a.status='A'
) TO '/tmp/attended.csv' WITH (FORMAT CSV, HEADER);