Postgres Querying

Work in Progress

This blog post as a temporary place for interesting Postgres querying.

Top ten speakers with video counts

Some videos have no speaker names, because I have not added them so I need to exclude them from the query.

SELECT
    speaker_name,
    count(*) AS total_videos
FROM
    all_events
WHERE
    speaker_name != ''
GROUP BY
    1
ORDER BY
    total_videos DESC
LIMIT 10;

Find duplicate tags

SELECT
    *
FROM (
    SELECT
        id,
        name,
        slug,
        count(1) OVER (PARTITION BY slug) AS Cnt
    FROM
        tags) a
WHERE
    Cnt > 1;

Find videos with title that has this word 'keynote'

SELECT * FROM videos WHERE title ~* 'keynote' AND provider = 'wwdc';

Find videos with title that has this word 'keynote' and another value

SELECT id, title FROM videos WHERE title ~* 'keynote' AND provider = 'wwdc';

Find total size of database

SELECT pg_size_pretty( pg_database_size('dbname') );

Total count for tags for each video

SELECT name, count(*) total_tags 
FROM tags 
JOIN videos_tags on videos_tags.tag_id = tags.id 
GROUP by tags.id 
ORDER by total_tags desc;

Total count for speakers for videos

select name, count(*) total 
from speakers 
join videos_speakers on videos_speakers.speaker_id = speakers.id 
group by speakers.id 
order by total desc
limit 10;