Helpful SQL queries for prostres

postgresql-query-examples.sql

-- A collection of sample queries in Postgres


-- Get demographic data for users

SELECT gender, Count(*) as genders from dante_userprofile as userprofile
JOIN applications_application as application
ON application.user_id=userprofile.user_id
WHERE application.is_submitted=True
-- WHERE is_finalist=True
GROUP BY gender
ORDER BY genders DESC;

-- Get a list of mentorship discount codes and the mentor and mentee user profiles
-- Left join to include rows that don't have a mentor or mentee

SELECT mentorship_discountcode.*, 
    userprofile.first_name as "mentee first name", userprofile.last_name as "mentee last name",
    mentor_user_profile.first_name as "mentor first name", mentor_user_profile.last_name as "mentor last name"
FROM mentorship_discountcode
LEFT JOIN mentorship_mentorshipsession as mentorshipsession
    ON mentorship_discountcode.session_id = mentorshipsession.id
LEFT JOIN dante_userprofile as userprofile
    ON mentorshipsession.mentee_id = userprofile.user_id
LEFT JOIN mentorship_mentor as mentorprofile
    ON mentorshipsession.mentor_id = mentorprofile.id
LEFT JOIN dante_userprofile as mentor_user_profile
    ON mentorprofile.user_id = mentor_user_profile.user_id



-- get all scholarships with a deadline of 18 months ago or less (less meaning further back)
-- get all scholarships with a deadline of 18 months from now or greater (indicates that default deadline is used)
-- random order so that all everyone assigned gets a mix of really old and default deadline scholarships
-- https://data.heroku.com/dataclips/atjbairawbwryppoorhbfvtrgatw
-- https://docs.google.com/spreadsheets/d/1QxKMTtvtbL-C3JNmAx_pSK3aPbRun8kGPo5n7cmZ4uE/edit#gid=0

SELECT name, deadline, slug, 'https://atila.ca/scholarship/' || slug as url from dante_scholarship
where deadline < now() - interval '18 months'
OR deadline > now() + interval '18 months'
ORDER BY RANDOM();

-- Similar to above, the differences are self explanatory
SELECT name, deadline, slug, 'https://atila.ca/scholarship/' || slug as url from dante_scholarship
where deadline < now() - interval '12 months'
OR deadline = '2022-12-31 23:59:00+00'
ORDER BY RANDOM();


SELECT user_id, user, first_name, last_name, gender,
 post_secondary_school, major, eligible_schools, eligible_programs,
 metadata->>'scholarship_cache' as scholarship_cache,
  scholarships_match_score, metadata FROM dante_userprofile;

SELECT user_id, user, first_name, last_name, gender,
date_created, date_time_created,
 post_secondary_school, major, eligible_schools, eligible_programs,
 metadata->>'scholarship_cache' as scholarship_cache,
  scholarships_match_score, metadata FROM dante_userprofile
WHERE is_debug_mode=true
or is_atila_admin=true
  ORDER BY user_id ASC;
  
--  Get all column's that don't have an empty json dict column
-- Not the use of double quotes to reference upper case column names
https://stackoverflow.com/questions/24292575/how-to-query-a-json-column-for-empty-objects/24296054
SELECT count(*) from listings_homeimage
WHERE "ImageVectors" <> '{}';

-- get rows where the email contains 'uwo.ca'
-- https://dba.stackexchange.com/questions/117609/looking-for-simple-contains-method-when-searching-text-in-postgresql 
SELECT user_id, date_created, email   FROM dante_userprofile
WHERE email LIKE '%uwo.ca%'
ORDER BY user_id
LIMIT 100


-- filter by column alias
-- filter by nested json field
-- https://stackoverflow.com/questions/3241352/using-an-alias-column-in-the-where-clause-in-postgresql
-- https://stackoverflow.com/questions/30896497/postgres-column-does-not-exist-but-its-there-with-alias
WITH user_metadata AS (
SELECT user_id, metadata
 ( metadata ->> 'batch_ids' ) as batch_ids
 FROM db_users
)
SELECT * FROM user_metadata
WHERE batch_ids IS NOT NULL
ORDER BY user_id DESC;

-- find all users that have covid in their user_profile.metadata['email_batches']
-- use ILIKE instead of LIKE to make it case insensitive
WITH user_data as (

SELECT user_id, user, first_name, last_name, gender,
date_created, date_time_created,
 post_secondary_school, major, eligible_schools, eligible_programs,
 metadata->>'email_batches' as email_batches,
  scholarships_match_score, metadata FROM dante_userprofile
)
SELECT * from user_data
WHERE email_batches LIKE '%covid%'
  ORDER BY user_id ASC;
  
  
-- find all users that have a NULL city for userprofile.metadata_private['geo_ip_registration']['city']
-- https://stackoverflow.com/a/24944474/5405197
-- https://www.postgresql.org/docs/current/functions-json.html

-- -> will preserve field into a json, while ->> will convert the field into text
-- https://www.postgresql.org/docs/current/functions-json.html

WITH user_data as (

SELECT user_id, user, first_name, last_name, gender,
date_created, date_time_created,
 post_secondary_school, major, eligible_schools, eligible_programs,
 metadata_private->'geo_ip_registration' as geo_ip_registration,
  scholarships_match_score, metadata FROM dante_userprofile
)
SELECT * from user_data
WHERE geo_ip_registration->'city' IS NULL
AND geo_ip_registration is NOT NULL
  ORDER BY user_id ASC;


-- Query using a JOIN on application, scholarship, and userprofile tables
SELECT scholarship.name, application.id, application.date_submitted, userprofile.first_name, userprofile.last_name, userprofile.email,
userprofile.is_debug_mode
FROM applications_application as application
JOIN dante_userprofile as userprofile
ON application.user_id=userprofile.user_id
JOIN dante_scholarship as scholarship
ON application.scholarship_id=scholarship.id


-- Get scholarship applications and nested JSON response
SELECT scholarship.name, application.id, application.date_submitted, application.is_submitted,
 userprofile.first_name, userprofile.last_name, userprofile.email,

 application.first_name_code, application.last_name_code,
 application.scholarship_responses,
 application.scholarship_responses ->  'what-are-your-plans-and-goals-for-the-future' ->> 'response' as "Future goals",
 application.scholarship_responses -> 'tell-us-about-a-project-or-skill-youre-working-on' ->> 'response' as "Side Project",
userprofile.is_debug_mode, 'https://atila.ca/application/' || application.id as application_url,
'https://atila.ca/scholarship/' || scholarship.slug as scholarship_url
FROM applications_application as application
JOIN dante_userprofile as userprofile
ON application.user_id=userprofile.user_id
JOIN dante_scholarship as scholarship
ON application.scholarship_id=scholarship.id
WHERE application.is_submitted=True