Common-Table Expressions (CTE) allow you to extract reusable pieces of SQL queries.
INTERSECT is the same as UNION, but only for rows that match between the two result sets. These also exists an EXCEPT for removing rows that are in the 2nd set.
EXCEPT is used to filter out the show's Twitter accounts and page from certain metrics:
-- Find all personas that exist, except the ones that belong to a show
SELECT persona_id
FROM personas
EXCEPT
SELECT persona_id
FROM show_twitter_accounts
JOIN twitter_personas USING (screen_name)
show_id | name | loyal_personas_count | all_personas_count | loyalty_percentage
--------------------------------------+---------------------------+----------------------+--------------------+------------------------
71691cfa-daec-11e0-9b8e-40402761cfca | TVA NOUVELLES | 1958 | 7563 | 25.8891974084358059
71694496-daec-11e0-9b8e-40402761cfca | On connaît la chanson | 134 | 563 | 23.8010657193605684
71687e9e-daec-11e0-9b8e-40402761cfca | 30 vies | 119 | 509 | 23.3791748526522593
71681dd2-daec-11e0-9b8e-40402761cfca | Un souper presque parfait | 755 | 3493 | 21.6146578872029774
71693bcc-daec-11e0-9b8e-40402761cfca | Le Gentleman | 18 | 87 | 20.6896551724137931
71692ae2-daec-11e0-9b8e-40402761cfca | Occupation double | 4843 | 23477 | 20.6287004302082890
71684488-daec-11e0-9b8e-40402761cfca | Et que ça saute | 37 | 181 | 20.4419889502762431
716a04f8-daec-11e0-9b8e-40402761cfca | M. Net | 117 | 593 | 19.7301854974704890
716930e6-daec-11e0-9b8e-40402761cfca | Yamaska | 118 | 613 | 19.2495921696574225
716849c4-daec-11e0-9b8e-40402761cfca | Opération Séduction | 153 | 795 | 19.2452830188679245
7168234a-daec-11e0-9b8e-40402761cfca | Duo | 8 | 43 | 18.6046511627906977
716927d6-daec-11e0-9b8e-40402761cfca | LA POULE AUX ŒUFS D’OR | 11 | 60 | 18.3333333333333333
71685220-daec-11e0-9b8e-40402761cfca | Des kiwis et des hommes | 14 | 77 | 18.1818181818181818
71681d28-daec-11e0-9b8e-40402761cfca | Dumont | 134 | 790 | 16.9620253164556962
71681c7e-daec-11e0-9b8e-40402761cfca | Atomes Crochus | 44 | 273 | 16.1172161172161172
7169d442-daec-11e0-9b8e-40402761cfca | Mirador | 40 | 261 | 15.3256704980842912
7168a284-daec-11e0-9b8e-40402761cfca | Le club des ex | 31 | 203 | 15.2709359605911330
7168248a-daec-11e0-9b8e-40402761cfca | Génération Inc. | 14 | 93 | 15.0537634408602151
7168b846-daec-11e0-9b8e-40402761cfca | C’est juste de la tv | 40 | 266 | 15.0375939849624060
716b272a-daec-11e0-9b8e-40402761cfca | bazzo.tv | 47 | 314 | 14.9681528662420382
71689eba-daec-11e0-9b8e-40402761cfca | Tout le monde en parle | 322 | 2278 | 14.1352063213345040
7169fe18-daec-11e0-9b8e-40402761cfca | Palmarès | 158 | 1171 | 13.4927412467976089
71684690-daec-11e0-9b8e-40402761cfca | Un gars le soir | 111 | 897 | 12.3745819397993311
7168f630-daec-11e0-9b8e-40402761cfca | Salut, Bonjour! | 406 | 3345 | 12.1375186846038864
71683ed4-daec-11e0-9b8e-40402761cfca | Les détestables | 42 | 361 | 11.6343490304709141
71687bec-daec-11e0-9b8e-40402761cfca | Le téléjournal 18h | 58 | 499 | 11.6232464929859719
716a0a20-daec-11e0-9b8e-40402761cfca | Déclic | 15 | 132 | 11.3636363636363636
fc29bac0-e554-012e-ebc3-40400fe46aa7 | Gala de l'ADISQ | 4 | 36 | 11.1111111111111111
71688308-daec-11e0-9b8e-40402761cfca | Les enfants de la télé | 8 | 77 | 10.3896103896103896
716911f6-daec-11e0-9b8e-40402761cfca | tout simplement clodine | 37 | 370 | 10.0000000000000000
716b4282-daec-11e0-9b8e-40402761cfca | Belle et Bum | 8 | 84 | 9.5238095238095238
7168414a-daec-11e0-9b8e-40402761cfca | Price is Right | 129 | 1402 | 9.2011412268188302
219b6d50-bf81-012e-eb84-40400fe46aa7 | les parents | 8 | 87 | 9.1954022988505747
71683f74-daec-11e0-9b8e-40402761cfca | La boutique MC Gilles | 47 | 512 | 9.1796875000000000
716a0674-daec-11e0-9b8e-40402761cfca | Cliptoman | 22 | 252 | 8.7301587301587302
7168ad7e-daec-11e0-9b8e-40402761cfca | Pour l’amour du country | 2 | 25 | 8.0000000000000000
7168f77a-daec-11e0-9b8e-40402761cfca | ça finit bien la semaine | 4 | 54 | 7.4074074074074074
7169cc22-daec-11e0-9b8e-40402761cfca | La Galère | 108 | 1540 | 7.0129870129870130
716a0200-daec-11e0-9b8e-40402761cfca | Sainte-Catherine | 3 | 45 | 6.6666666666666667
7168428a-daec-11e0-9b8e-40402761cfca | L'Arbitre | 1 | 15 | 6.6666666666666667
7168bb16-daec-11e0-9b8e-40402761cfca | Comme par magie | 2 | 30 | 6.6666666666666667
716841ea-daec-11e0-9b8e-40402761cfca | Remise à neuf | 38 | 579 | 6.5630397236614853
716823ea-daec-11e0-9b8e-40402761cfca | La guerre des clans | 23 | 461 | 4.9891540130151844
7169350a-daec-11e0-9b8e-40402761cfca | Fidèles au poste | 28 | 666 | 4.2042042042042042
71684014-daec-11e0-9b8e-40402761cfca | Taxi Payant | 1 | 37 | 2.7027027027027027
75bb43e0-df05-012e-ebbb-40400fe46aa7 | vlog | 5 | 199 | 2.5125628140703518
d427f470-dd5c-012e-ebb7-40400fe46aa7 | Star Académie | 11 | 2735 | 0.40219378427787934186