A key concept in "content analysis" methodologies over large temporally diverse archives is the notion of time-based random samples: creating constructed week or other random samples by month, year or other temporal resolutions. How might we do that easily at archive scale across millions of broadcasts spanning 24 years? In particular, as we test new kinds of analytic tools such as LMM ASR and vision systems and more advanced narrative analyses, we have a growing need to construct random samples by various date aggregations over selected channels to see how the tools' performance varies over time. Using our BigQuery + Bigtable GCS digital twin, compiling these sorts of random samples is trivial, from yearly and monthly samples to specific days of the week and even more bespoke aggregations like year-dayofweek.
Construct a yearly random sample of 10 shows from CNN for each year 2009-present is as trivial as the following SQL. For this particular example we were testing the performance of ccextractor over time, so we further limited ourselves to broadcasts that contained captioning (should be all CNN broadcasts, but this excludes any edge cases):
select DATE, count(1) cnt, STRING_AGG(id ORDER BY RAND() LIMIT 2) sampShows from (
select
JSON_EXTRACT_SCALAR(DOWN, '$.id') id, SAFE_CAST(substr(rowkey, 0, 4) as NUMERIC) DATE, DOWN
FROM (
SELECT
rowkey,
( select array(select value from unnest(cell))[OFFSET(0)] from unnest(cf.column) where name in ('DOWN') ) DOWN
FROM `[PROJECTID].bigtableconnections.digtwin` where SAFE_CAST(substr(rowkey, 0, 8) as NUMERIC) > 20000000 and SAFE_CAST(substr(rowkey, 0, 8) as NUMERIC) <= 20240831
)
) where JSON_EXTRACT_SCALAR(DOWN, '$.status') = 'SUCCESS' AND JSON_EXTRACT_SCALAR(DOWN, '$.metaCCNum') != '' AND JSON_EXTRACT_SCALAR(DOWN, '$.chan') in ('CNN') group by DATE order by DATE ASC
This yields the following list:
| DATE | cnt | sampShows |
| 2001 | 13 | CNN_20010915_040000_Larry_King_Live,CNN_20010912_070000_Larry_King_Live |
| 2009 | 2060 | CNN_20091125_000000_CNN_Tonight,CNN_20091124_030000_Anderson_Cooper_360 |
| 2010 | 5472 | CNN_20100622_160000,CNN_20100708_150000_CNN_Newsroom |
| 2011 | 10708 | CNN_20110105_160000_CNN_Newsroom,CNN_20110109_160000_Reliable_Sources |
| 2012 | 13266 | CNNW_20120203_080000_Piers_Morgan_Tonight,CNNW_20120930_080000_CNN_Newsroom |
| 2013 | 9050 | CNN_20131017_130000_CNN_Newsroom,CNNW_20130714_080000_Crimes_of_the_Century |
| 2014 | 7937 | CNNW_20140520_200000_The_Lead_With_Jake_Tapper,CNNW_20140121_090000_Anderson_Cooper_360 |
| 2015 | 7545 | CNNW_20150330_090000_Early_Start_With_John_Berman_and_Christine_Romans,CNNW_20150717_210000_The_Situation_Room |
| 2016 | 7517 | CNNW_20160131_190000_CNN_Newsroom_With_Fredricka_Whitfield,CNNW_20161207_140000_CNN_Newsroom_With_Carol_Costello |
| 2017 | 8813 | CNNW_20171014_060000_CNN_Tonight_With_Don_Lemon,CNNW_20170324_070000_CNN_Tonight_With_Don_Lemon |
| 2018 | 8683 | CNNW_20180630_220000_Smerconish,CNNW_20181115_230000_Situation_Room_With_Wolf_Blitzer |
| 2019 | 8623 | CNNW_20191211_220000_Situation_Room_With_Wolf_Blitzer,CNNW_20190120_230000_CNN_Newsroom_With_Ana_Cabrera |
| 2020 | 8414 | CNNW_20200808_100000_New_Day_Weekend_With_Victor_Blackwell_and_Christi_Paul,CNNW_20200106_070000_CNN_Newsroom_Live |
| 2021 | 8562 | CNNW_20210103_170000_State_of_the_Union_With_Jake_Tapper,CNNW_20211224_080000_CNN_Newsroom_Live |
| 2022 | 8565 | CNNW_20220211_090000_CNN_Newsroom_Live,CNNW_20221206_000000_Erin_Burnett_OutFront |
| 2023 | 8598 | CNNW_20230113_190000_CNN_Newsroom,CNNW_20230318_120000_CNN_This_Morning_Weekend |
| 2024 | 5588 | CNNW_20240105_170000_Inside_Politics_With_Dana_Bash,CNNW_20240703_040000_Anderson_Cooper_360 |
What about by month?
select DATE, count(1) cnt, STRING_AGG(id ORDER BY RAND() LIMIT 2) sampShows from (
select
JSON_EXTRACT_SCALAR(DOWN, '$.metaProgram') showName, JSON_EXTRACT_SCALAR(DOWN, '$.id') id, SAFE_CAST(substr(rowkey, 0, 6) as NUMERIC) DATE, DOWN
FROM (
SELECT
rowkey,
( select array(select value from unnest(cell))[OFFSET(0)] from unnest(cf.column) where name in ('DOWN') ) DOWN
FROM `[PROJECTID].bigtableconnections.digtwin` where SAFE_CAST(substr(rowkey, 0, 8) as NUMERIC) > 20000000 and SAFE_CAST(substr(rowkey, 0, 8) as NUMERIC) <= 20240831
)
) where JSON_EXTRACT_SCALAR(DOWN, '$.status') = 'SUCCESS' AND JSON_EXTRACT_SCALAR(DOWN, '$.metaCCNum') != '' AND JSON_EXTRACT_SCALAR(DOWN, '$.chan') in ('CNN') group by DATE order by DATE ASC
Yielding:
| DATE | cnt | sampShows |
| 200103 | 2 | CNN_20010314_080000_Larry_King_Live,CNN_20010314_050000_Larry_King_Live |
| 200108 | 2 | CNN_20010828_070000_Larry_King_Live,CNN_20010815_070000_Larry_King_Live |
| 200109 | 9 | CNN_20010908_040000_Larry_King_Live,CNN_20010915_070000_Larry_King_Live |
| 200907 | 491 | CNN_20090710_150000_CNN_Newsroom,CNN_20090731_040000_Larry_King_Live |
| 200908 | 522 | CNN_20090823_140000_John_King_Reliable_Sources,CNN_20090823_220000_CNN_Newsroom |
| 200909 | 332 | CNN_20090912_120000_CNN_Saturday_Morning,CNN_20090913_160000_State_of_the_Union_With_John_King |
| 200910 | 4 | CNN_20091014_223952,CNN_20091014_230000 |
| 200911 | 184 | CNN_20091122_130000_CNN_Sunday_Morning,CNN_20091125_060000_Anderson_Cooper_360 |
| 200912 | 527 | CNN_20091215_020000_Larry_King_Live,CNN_20091212_080000_Larry_King_Live |
| 201001 | 564 | CNN_20100109_030000_Anderson_Cooper_360,CNN_20100122_030000_Anderson_Cooper_360 |
| 201002 | 400 | CNN_20100206_020000_Larry_King_Live,CNN_20100209_080000_Larry_King_Live |
| 201003 | 425 | CNN_20100315_050000_CNN_Newsroom,CNN_20100318_080000_Campbell_Brown |
| 201004 | 417 | CNN_20100415_230000_John_King_USA,CNN_20100412_070000_Larry_King_Live |
| 201005 | 295 | CNN_20100502_220000_CNN_Newsroom,CNN_20100515_140000_CNN_Newsroom |
| 201006 | 370 | CNN_20100621_043000,CNN_20100621_080000 |
| 201007 | 510 | CNN_20100727_040000_Larry_King_Live,CNN_20100702_050000_Anderson_Cooper_360 |
| 201008 | 476 | CNN_20100821_070000_Larry_King_Live,CNN_20100821_150000_CNN_Newsroom |
| 201009 | 467 | CNN_20100919_020000_CNN_Newsroom,CNN_20100921_230000_John_King_USA |
| 201010 | 533 | CNN_20101030_080000_Parker_Spitzer,CNN_20101023_010000_Larry_King_Live |
| 201011 | 493 | CNN_20101119_010000_Parker_Spitzer,CNN_20101119_080000_Larry_King_Live |
| 201012 | 522 | CNN_20101208_160000_CNN_Newsroom,CNN_20101214_100000_Anderson_Cooper_360 |
| 201101 | 547 | CNN_20110117_000000_CNN_Newsroom,CNN_20110104_110000_American_Morning |
| 201102 | 492 | CNN_20110210_060000_Anderson_Cooper_360,CNN_20110214_200000_CNN_Newsroom |
| 201103 | 527 | CNN_20110319_020000_Anderson_Cooper_360,CNN_20110319_113000_Sanjay_Gupta_MD |
| 201104 | 541 | CNN_20110416_040000_Piers_Morgan_Tonight,CNN_20110406_070000_Piers_Morgan_Tonight |
| 201105 | 885 | CNN_20110517_130000_CNN_Newsroom,CNN_20110512_050000_Anderson_Cooper_360 |
| 201106 | 1055 | CNNW_20110625_060000_Anderson_Cooper_360,CNNW_20110604_113000_Sanjay_Gupta_MD |
| 201107 | 1078 | CNNW_20110719_050000_Anderson_Cooper_360,CNNW_20110707_190000_CNN_Newsroom |
| 201108 | 1045 | CNNW_20110820_150000_CNN_Newsroom,CNNW_20110819_161055_CNN_Newsroom |
| 201109 | 1023 | CNN_20110926_090000_AM_Wake_Up_Call,CNNW_20110916_150000_CNN_Newsroom |
| 201110 | 1204 | CNNW_20111005_060000_Anderson_Cooper_360,CNN_20111025_000000_Anderson_Cooper_360 |
| 201111 | 1137 | CNNW_20111113_000000_CNN_Newsroom,CNN_20111101_020000_Anderson_Cooper_360 |
| 201112 | 1174 | CNN_20111212_140000_CNN_Newsroom,CNNW_20111201_230000_John_King_USA |
| 201201 | 1167 | CNN_20120121_100000_Piers_Morgan_Tonight,CNNW_20120130_230000_John_King_USA |
| 201202 | 1089 | CNN_20120204_220000_CNN_Newsroom,CNNW_20120218_150000_CNN_Newsroom |
| 201203 | 1209 | CNNW_20120325_200000_CNN_Newsroom,CNNW_20120324_140000_CNN_Newsroom |
| 201204 | 1201 | CNNW_20120414_160000_CNN_Newsroom,CNN_20120419_230000_Erin_Burnett_OutFront |
| 201205 | 1212 | CNNW_20120527_050000_CNN_Newsroom,CNNW_20120524_020000_Anderson_Cooper_360 |
| 201206 | 1161 | CNNW_20120610_050000_CNN_Newsroom,CNN_20120622_060000_Erin_Burnett_OutFront |
| 201207 | 1217 | CNN_20120719_130000_CNN_Newsroom,CNN_20120710_010000_Piers_Morgan_Tonight |
| 201208 | 1184 | CNN_20120806_000000_CNN_Presents,CNN_20120801_150000_CNN_Newsroom |
| 201209 | 1136 | CNNW_20120923_230000_CNN_Newsroom,CNNW_20120914_150000_CNN_Newsroom |
| 201210 | 1101 | CNN_20121025_080000_Anderson_Cooper_360,CNN_20121009_070000_Piers_Morgan_Tonight |
| 201211 | 905 | CNNW_20121111_150000_Fareed_Zakaria_GPS,CNN_20121111_170000_State_of_the_Union |
| 201212 | 684 | CNNW_20121220_100000_EarlyStart,CNNW_20121209_210000_CNN_Newsroom |
| 201301 | 836 | CNNW_20130103_100000_EarlyStart,CNNW_20130107_170000_CNN_Newsroom |
| 201302 | 1022 | CNNW_20130218_170000_CNN_Newsroom,CNNW_20130210_200000_Your_Money |
| 201303 | 637 | CNNW_20130322_040000_Piers_Morgan_Live,CNNW_20130320_040000_Piers_Morgan_Live |
| 201304 | 667 | CNNW_20130406_180000_CNN_Newsroom,CNNW_20130401_130000_CNN_Newsroom |
| 201305 | 682 | CNNW_20130504_030000_Anthony_Bourdain_Parts_Unknown,CNNW_20130527_230000_Erin_Burnett_OutFront |
| 201306 | 678 | CNNW_20130619_160000_CNN_Newsroom,CNN_20130617_200000_The_Lead_With_Jake_Tapper |
| 201307 | 664 | CNNW_20130727_170000_CNN_Newsroom,CNNW_20130718_100000_New_Day |
| 201308 | 835 | CNN_20130809_070000_Piers_Morgan_Live,CNNW_20130824_140000_CNN_Saturday_Morning |
| 201309 | 830 | CNNW_20130907_133000_Your_Money,CNNW_20130915_160000_State_of_the_Union |
| 201310 | 942 | CNN_20131003_180000_CNN_Newsroom,CNNW_20131025_150000_Legal_View_With_Ashleigh_Banfield |
| 201311 | 621 | CNNW_20131104_090000_Inside_Man,CNNW_20131102_090000_Anthony_Bourdain_Parts_Unknown |
| 201312 | 636 | CNNW_20131206_000000_Erin_Burnett_OutFront,CNNW_20131226_060000_CNN_Presents |
| 201401 | 639 | CNNW_20140120_180000_CNN_Newsroom,CNNW_20140131_060000_The_Sixties_The_British_Invasion |
| 201402 | 619 | CNNW_20140221_010000_Anderson_Cooper_360,CNNW_20140218_083000_Forensic_Files |
| 201403 | 679 | CNNW_20140311_230000_Erin_Burnett_OutFront,CNNW_20140329_020000_CNN_Special_Report |
| 201404 | 623 | CNNW_20140428_170000_Wolf,CNNW_20140416_020000_CNN_Special_Report |
| 201405 | 653 | CNNW_20140505_130000_CNN_Newsroom,CNNW_20140501_210000_The_Situation_Room |
| 201406 | 713 | CNNW_20140609_200000_The_Lead_With_Jake_Tapper,CNNW_20140622_093000_Forensic_Files |
| 201407 | 743 | CNNW_20140716_170000_Wolf,CNNW_20140717_070000_Forensic_Files |
| 201408 | 675 | CNNW_20140805_053000_African_Voices,CNNW_20140809_000000_Anderson_Cooper_360 |
| 201409 | 636 | CNNW_20140911_000000_Anderson_Cooper_360,CNNW_20140912_150000_At_This_Hour_With_Berman_and_Michaela |
| 201410 | 668 | CNNW_20141004_030000_Anthony_Bourdain_Parts_Unknown,CNNW_20141026_123000_Inside_Politics |
| 201411 | 638 | CNNW_20141106_110000_New_Day,CNNW_20141106_190000_CNN_Newsroom_With_Brooke_Baldwin |
| 201412 | 651 | CNNW_20141207_020000_Somebodys_Gotta_Do_It_With_Mike_Rowe,CNNW_20141221_090000_CNNI_Simulcast |
| 201501 | 659 | CNNW_20150125_080000_Forensic_Files,CNNW_20150114_070000_CNNI_Simulcast |
| 201502 | 610 | CNNW_20150204_070000_CNNI_Simulcast,CNNW_20150212_040000_Anderson_Cooper_360 |
| 201503 | 653 | CNNW_20150331_070000_CNN_International,CNNW_20150305_170000_Legal_View_With_Ashleigh_Banfield |
| 201504 | 642 | CNNW_20150420_020000_High_Profits,CNNW_20150416_040000_Anthony_Bourdain_Parts_Unknown |
| 201505 | 674 | CNNW_20150519_180000_CNN_Newsroom_With_Brooke_Baldwin,CNNW_20150507_090000_Early_Start_With_John_Berman_and_Christine_Romans |
| 201506 | 737 | CNNW_20150622_140000_CNN_Newsroom_With_Carol_Costello,CNNW_20150622_120000_New_Day |
| 201507 | 722 | CNNW_20150720_120000_New_Day,CNNW_20150710_090000_Early_Start_With_John_Berman_and_Christine_Romans |
| 201508 | 586 | CNNW_20150830_210000_CNN_Newsroom_With_Poppy_Harlow,CNNW_20150826_030000_Anderson_Cooper_360 |
| 201509 | 550 | CNNW_20150902_170000_Wolf,CNNW_20150914_200000_The_Lead_With_Jake_Tapper |
| 201510 | 579 | CNNW_20151001_160000_Legal_View_With_Ashleigh_Banfield,CNNW_20151010_100000_New_Day_Saturday |
| 201511 | 556 | CNNW_20151118_000000_Erin_Burnett_OutFront,CNNW_20151113_020000_CNN_Special_Report |
| 201512 | 577 | CNNW_20151224_000000_Erin_Burnett_OutFront,CNNW_20151211_010000_Anderson_Cooper_360 |
| 201601 | 576 | CNNW_20160109_140000_Smerconish,CNNW_20160117_000000_CNN_Newsroom_With_Poppy_Harlow |
| 201602 | 539 | CNNW_20160211_110000_New_Day,CNNW_20160206_170000_CNN_Newsroom_With_Fredricka_Whitfield |
| 201603 | 595 | CNNW_20160320_070000_Anthony_Bourdain_Parts_Unknown,CNNW_20160327_100000_New_Day_Sunday |
| 201604 | 580 | CNNW_20160413_100000_New_Day,CNNW_20160430_210000_CNN_Newsroom_With_Poppy_Harlow |
| 201605 | 586 | CNNW_20160503_033000_WE_GOT_HIM_Obama_Bin_Laden_the_War_On_Terror,CNNW_20160528_220000_Smerconish |
| 201606 | 575 | CNNW_20160621_010000_Why_They_Hate_Us,CNNW_20160628_010000_Anderson_Cooper_360 |
| 201607 | 579 | CNNW_20160731_080000_CNN_Newsroom_Live,CNNW_20160704_200000_The_Lead_With_Jake_Tapper |
| 201608 | 612 | CNNW_20160830_070000_CNN_Tonight_With_Don_Lemon,CNNW_20160809_200000_The_Lead_With_Jake_Tapper |
| 201609 | 708 | CNNW_20160913_100000_New_Day,CNNW_20160918_190000_CNN_Newsroom_With_Fredricka_Whitfield |
| 201610 | 719 | CNNW_20161021_040000_Anderson_Cooper_360,CNNW_20161029_120000_New_Day_Saturday |
| 201611 | 721 | CNNW_20161118_090000_Early_Start_With_John_Berman_and_Christine_Romans,CNNW_20161120_190000_CNN_Newsroom_With_Fredricka_Whitfield |
| 201612 | 727 | CNNW_20161206_080000_CNN_Tonight_With_Don_Lemon,CNNW_20161205_170000_CNN_Newsroom_With_Brianna_Keilar |
| 201701 | 729 | CNNW_20170131_160000_At_This_Hour_With_Berman_and_Bolduan,CNNW_20170112_100000_Early_Start_With_John_Berman_and_Christine_Romans |
| 201702 | 694 | CNNW_20170201_140000_CNN_Newsroom_With_Carol_Costello,CNNW_20170219_220000_CNN_Newsroom |
| 201703 | 787 | CNNW_20170312_210000_CNN_Newsroom_With_Ana_Cabrera,CNNW_20170326_230000_CNN_Newsroom_With_Ana_Cabrera |
| 201704 | 743 | CNNW_20170412_180000_CNN_Newsroom_With_Brooke_Baldwin,CNNW_20170412_160000_Inside_Politics |
| 201705 | 746 | CNNW_20170509_160000_Inside_Politics,CNNW_20170505_220000_Situation_Room_With_Wolf_Blitzer |
| 201706 | 720 | CNNW_20170602_080000_Early_Start_with_Christine_Romans_and_Dave_Briggs,CNNW_20170605_130000_CNN_Newsroom_With_John_Berman_and_Poppy_Harlow |
| 201707 | 732 | CNNW_20170729_100000_New_Day_Saturday,CNNW_20170708_040000_The_Seventies |
| 201708 | 740 | CNNW_20170825_060000_CNN_Films_Elian,CNNW_20170818_000000_Anderson_Cooper_360 |
| 201709 | 714 | CNNW_20170914_120000_New_Day,CNNW_20170925_170000_Wolf |
| 201710 | 745 | CNNW_20171012_150000_At_This_Hour_With_Kate_Bolduan,CNNW_20171004_010000_Anderson_Cooper_360 |
| 201711 | 718 | CNNW_20171101_080000_Early_Start_with_Christine_Romans_and_Dave_Briggs,CNNW_20171112_100000_CNN_Newsroom_Live |
| 201712 | 745 | CNNW_20171211_060000_Anthony_Bourdain_Parts_Unknown,CNNW_20171207_010000_Anderson_Cooper_360 |
| 201801 | 731 | CNNW_20180121_000000_Erin_Burnett_OutFront,CNNW_20180120_020000_Cuomo_Primetime |
| 201802 | 671 | CNNW_20180226_130000_New_Day,CNNW_20180211_230000_CNN_Newsroom_With_Ana_Cabrera |
| 201803 | 745 | CNNW_20180302_020000_Anderson_Cooper_360,CNNW_20180316_200000_The_Lead_With_Jake_Tapper |
| 201804 | 719 | CNNW_20180423_090000_Early_Start_with_Christine_Romans_and_Dave_Briggs,CNNW_20180416_110000_New_Day_With_Chris_Cuomo_and_Alisyn_Camerota |
| 201805 | 722 | CNNW_20180508_095900_New_Day_With_Chris_Cuomo_and_Alisyn_Camerota,CNNW_20180531_020000_CNN_Tonight_With_Don_Lemon |
| 201806 | 721 | CNNW_20180606_080000_Early_Start_with_Christine_Romans_and_Dave_Briggs,CNNW_20180605_040000_Anderson_Cooper_360 |
| 201807 | 731 | CNNW_20180713_010000_Cuomo_Primetime,CNNW_20180729_150000_Reliable_Sources |
| 201808 | 739 | CNNW_20180807_050000_Cuomo_Primetime,CNNW_20180810_230000_Erin_Burnett_OutFront |
| 201809 | 709 | CNNW_20180921_180000_CNN_Newsroom_With_Brooke_Baldwin,CNNW_20180915_210000_CNN_Newsroom_With_Ana_Cabrera |
| 201810 | 744 | CNNW_20181016_060000_CNN_Tonight_With_Don_Lemon,CNNW_20181011_030000_CNN_Tonight_With_Don_Lemon |
| 201811 | 719 | CNNW_20181119_160000_At_This_Hour_With_Kate_Bolduan,CNNW_20181126_160000_At_This_Hour_With_Kate_Bolduan |
| 201812 | 732 | CNNW_20181231_030000_The_2000s,CNNW_20181212_080000_CNN_Tonight_With_Don_Lemon |
| 201901 | 736 | CNNW_20190112_000000_Erin_Burnett_OutFront,CNNW_20190122_220000_Situation_Room_With_Wolf_Blitzer |
| 201902 | 665 | CNNW_20190222_200000_CNN_Newsroom_With_Brooke_Baldwin,CNNW_20190209_010000_Anderson_Cooper_360 |
| 201903 | 741 | CNNW_20190329_030000_CNN_Tonight_With_Don_Lemon,CNNW_20190320_140000_CNN_Newsroom_with_Poppy_Harlow_and_Jim_Sciutto |
| 201904 | 716 | CNNW_20190428_030000_The_Eighties,CNNW_20190418_040000_Anderson_Cooper_360 |
| 201905 | 744 | CNNW_20190513_080000_Early_Start_with_Christine_Romans_and_Dave_Briggs,CNNW_20190504_070000_CNN_Newsroom_Live |
| 201906 | 716 | CNNW_20190605_130000_CNN_Newsroom_with_Poppy_Harlow_and_Jim_Sciutto,CNNW_20190624_030000_CNN_Films |
| 201907 | 722 | CNNW_20190705_160000_Inside_Politics,CNNW_20190703_050000_CNN_Tonight_With_Don_Lemon |
| 201908 | 725 | CNNW_20190802_020000_CNN_Tonight_With_Don_Lemon,CNNW_20190823_210000_Situation_Room_With_Wolf_Blitzer |
| 201909 | 716 | CNNW_20190912_060000_CNN_Tonight_With_Don_Lemon,CNNW_20190910_130000_CNN_Newsroom_with_Poppy_Harlow_and_Jim_Sciutto |
| 201910 | 743 | CNNW_20191020_220000_CNN_Newsroom_With_Ana_Cabrera,CNNW_20191027_190000_CNN_Newsroom_With_Fredricka_Whitfield |
| 201911 | 687 | CNNW_20191115_050000_Impeachment_Inquiry_Special_Coverage,CNNW_20191103_070000_The_Nineties |
| 201912 | 712 | CNNW_20191208_230000_CNN_Newsroom_With_Ana_Cabrera,CNNW_20191222_140000_State_of_the_Union_With_Jake_Tapper |
| 202001 | 640 | CNNW_20200124_150000_The_Impeachment_Trial_of_Donald_J._Trump,CNNW_20200105_050000_Linda_Ronstadt_The_Sound_of_My_Voice |
| 202002 | 658 | CNNW_20200201_020000_Cuomo_Prime_Time,CNNW_20200210_210000_The_Lead_With_Jake_Tapper |
| 202003 | 700 | CNNW_20200303_130000_New_Day_With_Alisyn_Camerota_and_John_Berman,CNNW_20200314_070000_CNN_Tonight_With_Don_Lemon |
| 202004 | 708 | CNNW_20200413_190000_CNN_Newsroom_With_Brooke_Baldwin,CNNW_20200423_130000_CNN_Newsroom_With_Poppy_Harlow_and_Jim_Sciutto |
| 202005 | 730 | CNNW_20200507_040000_Anderson_Cooper_360,CNNW_20200510_110000_New_Day_Weekend_With_Victor_Blackwell_and_Christi_Paul |
| 202006 | 722 | CNNW_20200627_200000_CNN_Newsroom_With_Ana_Cabrera,CNNW_20200614_050000_CNN_Newsroom_Live |
| 202007 | 743 | CNNW_20200729_150000_CNN_Newsroom,CNNW_20200727_095900_New_Day_With_Alisyn_Camerota_and_John_Berman |
| 202008 | 732 | CNNW_20200818_180000_CNN_Newsroom,CNNW_20200821_000000_Americas_Choice_2020_Democratic_National_Convention |
| 202009 | 700 | CNNW_20200905_200000_CNN_Newsroom_With_Ana_Cabrera,CNNW_20200915_010000_Cuomo_Prime_Time |
| 202010 | 723 | CNNW_20201003_200000_CNN_Newsroom_With_Ana_Cabrera,CNNW_20201030_230000_Erin_Burnett_OutFront |
| 202011 | 622 | CNNW_20201119_100000_Early_Start_With_Christine_Romans_and_Laura_Jarrett,CNNW_20201121_140000_Smerconish |
| 202012 | 736 | CNNW_20201204_080000_CNN_Tonight_with_Don_Lemon,CNNW_20201204_140000_CNN_Newsroom_With_Poppy_Harlow_and_Jim_Sciutto |
| 202101 | 677 | CNNW_20210122_090000_CNN_Newsroom_Live,CNNW_20210106_120000_New_Day_With_Alisyn_Camerota_and_John_Berman |
| 202102 | 637 | CNNW_20210220_110000_New_Day_Weekend_With_Victor_Blackwell_and_Christi_Paul,CNNW_20210228_130000_Inside_Politics_with_Abby_Phillip |
| 202103 | 742 | CNNW_20210316_170000_CNN_Newsroom_With_Brianna_Keilar,CNNW_20210301_230000_The_Situation_Room_With_Wolf_Blitzer |
| 202104 | 714 | CNNW_20210409_010000_Cuomo_Prime_Time,CNNW_20210414_160000_Inside_Politics |
| 202105 | 739 | CNNW_20210516_150000_Reliable_Sources_with_Brian_Stelter,CNNW_20210513_180000_CNN_Newsroom_With_Alisyn_Camerota_and_Victor_Blackwell |
| 202106 | 705 | CNNW_20210614_150000_At_This_Hour_With_Kate_Bolduan,CNNW_20210628_095900_New_Day_With_John_Berman_and_Brianna_Keilar |
| 202107 | 730 | CNNW_20210724_000000_Anderson_Cooper_360,CNNW_20210712_080000_CNN_Newsroom_Live |
| 202108 | 741 | CNNW_20210810_130000_CNN_Newsroom_With_Poppy_Harlow_and_Jim_Sciutto,CNNW_20210831_170000_CNN_Newsroom_With_Ana_Cabrera |
| 202109 | 709 | CNNW_20210907_200000_The_Lead_With_Jake_Tapper,CNNW_20210913_150000_At_This_Hour_With_Kate_Bolduan |
| 202110 | 726 | CNNW_20211015_020000_Don_Lemon_Tonight,CNNW_20211028_190000_CNN_Newsroom_With_Alisyn_Camerota_and_Victor_Blackwell |
| 202111 | 706 | CNNW_20211101_010000_Diana,CNNW_20211111_105900_New_Day_With_John_Berman_and_Brianna_Keilar |
| 202112 | 736 | CNNW_20211219_000000_CNN_Newsroom_With_Pamela_Brown,CNNW_20211226_120000_New_Day_Weekend_With_Christi_Paul_and_Boris_Sanchez |
| 202201 | 727 | CNNW_20220126_120000_New_Day_With_John_Berman_and_Brianna_Keilar,CNNW_20220103_150000_CNN_Newsroom_With_Poppy_Harlow_and_Jim_Sciutto |
| 202202 | 670 | CNNW_20220214_180000_CNN_Newsroom_With_Ana_Cabrera,CNNW_20220219_230000_CNN_Newsroom_With_Pamela_Brown |
| 202203 | 735 | CNNW_20220306_000000_CNN_Newsroom_With_Pamela_Brown,CNNW_20220326_190000_CNN_Newsroom_With_Jim_Acosta |
| 202204 | 715 | CNNW_20220428_070000_CNN_Newsroom_Live,CNNW_20220413_120000_New_Day_With_John_Berman_and_Brianna_Keilar |
| 202205 | 730 | CNNW_20220522_210000_CNN_Newsroom_With_Jim_Acosta,CNNW_20220501_120000_Inside_Politics_With_Abby_Phillip |
| 202206 | 691 | CNNW_20220621_130000_CNN_Newsroom_With_Poppy_Harlow_and_Jim_Sciutto,CNNW_20220618_170000_CNN_Newsroom_With_Fredricka_Whitfield |
| 202207 | 720 | CNNW_20220722_040000_Don_Lemon_Tonight,CNNW_20220704_080000_CNN_Newsroom_Live |
| 202208 | 741 | CNNW_20220810_160000_Inside_Politics_With_John_King,CNNW_20220825_230000_Erin_Burnett_OutFront |
| 202209 | 701 | CNNW_20220919_120000_The_State_Funeral_of_Queen_Elizabeth_II,CNNW_20220911_120000_New_Day_Weekend |
| 202210 | 733 | CNNW_20221012_180000_CNN_Newsroom,CNNW_20221006_070000_CNN_Newsroom_Live |
| 202211 | 673 | CNNW_20221126_180000_CNN_Newsroom_With_Fredricka_Whitfield,CNNW_20221116_020000_Anderson_Cooper_360 |
| 202212 | 729 | CNNW_20221220_200000_CNN_Newsroom,CNNW_20221212_150000_CNN_Newsroom |
| 202301 | 731 | CNNW_20230117_070000_CNN_Tonight,CNNW_20230129_230000_CNN_Newsroom_With_Pamela_Brown |
| 202302 | 658 | CNNW_20230208_000000_Erin_Burnett_OutFront,CNNW_20230220_110000_CNN_This_Morning |
| 202303 | 733 | CNNW_20230308_010000_Anderson_Cooper_360,CNNW_20230318_130000_Smerconish |
| 202304 | 705 | CNNW_20230425_200000_The_Lead_With_Jake_Tapper,CNNW_20230405_170000_CNN_Newsroom |
| 202305 | 723 | CNNW_20230510_140000_CNN_News_Central,CNNW_20230505_180000_CNN_News_Central |
| 202306 | 704 | CNNW_20230625_220000_CNN_Newsroom_With_Jim_Acosta,CNNW_20230627_220000_The_Situation_Room_With_Wolf_Blitzer |
| 202307 | 732 | CNNW_20230730_230000_CNN_Newsroom_With_Jim_Acosta,CNNW_20230716_050000_Very_Scary_People |
| 202308 | 731 | CNNW_20230802_170000_CNN_News_Central,CNNW_20230813_040000_The_Seventies |
| 202309 | 702 | CNNW_20230920_130000_CNN_News_Central,CNNW_20230918_100000_CNN_This_Morning |
| 202310 | 744 | CNNW_20231004_040000_Anderson_Cooper_360,CNNW_20231003_130000_CNN_News_Central |
| 202311 | 707 | CNNW_20231105_010000_The_Whole_Story_With_Anderson_Cooper,CNNW_20231101_000000_Anderson_Cooper_360 |
| 202312 | 728 | CNNW_20231201_220000_The_Lead_With_Jake_Tapper,CNNW_20231201_200000_CNN_News_Central |
| 202401 | 702 | CNNW_20240127_200000_CNN_Newsroom_With_Fredricka_Whitfield,CNNW_20240112_170000_Inside_Politics_With_Dana_Bash |
| 202402 | 669 | CNNW_20240227_120000_CNN_News_Central,CNNW_20240207_150000_CNN_News_Central |
| 202403 | 725 | CNNW_20240315_140000_CNN_Newsroom_With_Jim_Acosta,CNNW_20240324_050000_CNN_Newsroom_Live |
| 202404 | 686 | CNNW_20240417_030000_Laura_Coates_Live,CNNW_20240423_080000_Erin_Burnett_OutFront |
| 202405 | 682 | CNNW_20240502_070000_Anderson_Cooper_360,CNNW_20240526_190000_CNN_Newsroom_With_Fredricka_Whitfield |
| 202406 | 710 | CNNW_20240623_220000_CNN_Newsroom,CNNW_20240630_060000_United_States_of_Scandal |
| 202407 | 698 | CNNW_20240718_120000_CNN_News_Central,CNNW_20240730_220000_The_Situation_Room_With_Wolf_Blitzer |
| 202408 | 716 | CNNW_20240819_000000_Anderson_Cooper_360,CNNW_20240802_090000_CNN_This_Morning |
Alternatively, instead of extracting the UTC recording date from the broadcast ID, we can switch to BigQuery's date parsing routines and leverage any imaginable date criteria.
What if we want shows by year, but only from Tuesdays, Wednesdays and Thursdays of each year?
select date, count(1) cnt, STRING_AGG(id ORDER BY RAND() LIMIT 2) sampShows from (
select
FORMAT_DATETIME("%a", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) dateDayOfWeek,
FORMAT_DATETIME("%h", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) dateMon,
FORMAT_DATETIME("%U", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) dateWeekNum,
FORMAT_DATETIME("%k", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) dateHour,
FORMAT_DATETIME("%Y%m%d%H%M%S", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) dateYYYMMDD,
FORMAT_DATETIME("%Y", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) date,
JSON_EXTRACT_SCALAR(DOWN, '$.id') id,
DOWN
FROM (
SELECT
rowkey,
( select array(select value from unnest(cell))[OFFSET(0)] from unnest(cf.column) where name in ('DOWN') ) DOWN
FROM `[PROJECTID].bigtableconnections.digtwin` where SAFE_CAST(substr(rowkey, 0, 8) as NUMERIC) > 20000000 and SAFE_CAST(substr(rowkey, 0, 8) as NUMERIC) <= 20240831
)
) where JSON_EXTRACT_SCALAR(DOWN, '$.status') = 'SUCCESS' AND JSON_EXTRACT_SCALAR(DOWN, '$.metaCCNum') != '' AND JSON_EXTRACT_SCALAR(DOWN, '$.chan') in ('CNN')
AND dateDayOfWeek in ('Tue','Wed','Thur')
group by date order by date ASC
Yielding:
| date | cnt | sampShows |
| 2001 | 7 | CNN_20010314_080000_Larry_King_Live,CNN_20010314_050000_Larry_King_Live |
| 2009 | 550 | CNN_20090811_040000_Larry_King_Live,CNN_20091201_110000_American_Morning |
| 2010 | 1372 | CNN_20100922_010000_Larry_King_Live,CNN_20100427_050000_Anderson_Cooper_360 |
| 2011 | 2830 | CNN_20111213_100000_AM_Wake_Up_Call,CNNW_20110810_090000_AM_Wake_Up_Call |
| 2012 | 3374 | CNNW_20120215_230000_John_King_USA,CNNW_20120606_190000_CNN_Newsroom |
| 2013 | 2415 | CNNW_20130306_160000_CNN_Newsroom,CNNW_20130214_070000_Erin_Burnett_OutFront |
| 2014 | 2235 | CNNW_20140121_210000_The_Lead_With_Jake_Tapper,CNNW_20140318_200000_The_Lead_With_Jake_Tapper |
| 2015 | 2053 | CNNW_20150218_180000_Wolf,CNNW_20150616_140000_CNN_Newsroom_With_Carol_Costello |
| 2016 | 2095 | CNNW_20160406_170000_Wolf,CNNW_20160517_210000_Situation_Room_With_Wolf_Blitzer |
| 2017 | 2529 | CNNW_20171024_110000_New_Day,CNNW_20170502_090000_Early_Start_with_Christine_Romans_and_Dave_Briggs |
| 2018 | 2489 | CNNW_20180308_010000_Anderson_Cooper_360,CNNW_20180823_010000_Cuomo_Primetime |
| 2019 | 2475 | CNNW_20191231_110000_New_Day_With_Alisyn_Camerota_and_John_Berman,CNNW_20191212_020000_Cuomo_Prime_Time |
| 2020 | 2389 | CNNW_20200514_030000_CNN_Tonight_with_Don_Lemon,CNNW_20200506_070000_CNN_Tonight_with_Don_Lemon |
| 2021 | 2419 | CNNW_20210707_070000_CNN_Newsroom_Live,CNNW_20211005_230000_Erin_Burnett_OutFront |
| 2022 | 2420 | CNNW_20220629_110000_New_Day_With_John_Berman_and_Brianna_Keilar,CNNW_20220104_120000_New_Day_With_John_Berman_and_Brianna_Keilar |
| 2023 | 2450 | CNNW_20230802_080000_CNN_Newsroom_Live,CNNW_20230726_180000_CNN_News_Central |
| 2024 | 1572 | CNNW_20240502_050000_Erin_Burnett_OutFront,CNNW_20240227_230000_The_Situation_Room_With_Wolf_Blitzer |
Instead of limiting ourselves to specific dates of the week, what about modifying our "date" field to concatenate the year and day of week together so that we get a random selection of shows by day of week by year in a form of constructed week?
select date, count(1) cnt, STRING_AGG(id ORDER BY RAND() LIMIT 2) sampShows from (
select
FORMAT_DATETIME("%a", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) dateDayOfWeek,
FORMAT_DATETIME("%h", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) dateMon,
FORMAT_DATETIME("%U", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) dateWeekNum,
FORMAT_DATETIME("%k", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) dateHour,
FORMAT_DATETIME("%Y%m%d%H%M%S", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) dateYYYMMDD,
FORMAT_DATETIME("%Y-%a", PARSE_DATETIME("%Y-%m-%d %H:%M:%S", JSON_EXTRACT_SCALAR(DOWN, '$.metaStartTimeLocal'))) date,
JSON_EXTRACT_SCALAR(DOWN, '$.id') id,
DOWN
FROM (
SELECT
rowkey,
( select array(select value from unnest(cell))[OFFSET(0)] from unnest(cf.column) where name in ('DOWN') ) DOWN
FROM `[PROJECTID].bigtableconnections.digtwin` where SAFE_CAST(substr(rowkey, 0, 8) as NUMERIC) > 20000000 and SAFE_CAST(substr(rowkey, 0, 8) as NUMERIC) <= 20240831
)
) where JSON_EXTRACT_SCALAR(DOWN, '$.status') = 'SUCCESS' AND JSON_EXTRACT_SCALAR(DOWN, '$.metaCCNum') != '' AND JSON_EXTRACT_SCALAR(DOWN, '$.chan') in ('CNN')
group by date order by date ASC
Yielding:
| date | cnt | sampShows |
| 2001-Fri | 1 | CNN_20010915_010000_Larry_King_Live |
| 2001-Mon | 1 | CNN_20010911_010000_Larry_King_Live |
| 2001-Sat | 3 | CNN_20010915_040000_Larry_King_Live,CNN_20010908_040000_Larry_King_Live |
| 2001-Thu | 1 | CNN_20010913_070000_Larry_King_Live |
| 2001-Tue | 3 | CNN_20010911_070000_Larry_King_Live,CNN_20010828_070000_Larry_King_Live |
| 2001-Wed | 4 | CNN_20010815_070000_Larry_King_Live,CNN_20010314_050000_Larry_King_Live |
| 2009-Fri | 257 | CNN_20090821_050000_Anderson_Cooper_360,CNN_20090731_060000_Anderson_Cooper_360 |
| 2009-Mon | 256 | CNN_20090831_060000_State_of_the_Union_With_John_King,CNN_20090914_170000_CNN_Newsroom |
| 2009-Sat | 360 | CNN_20091128_130000_CNN_Saturday_Morning,CNN_20090822_230000_CNN_Newsroom |
| 2009-Sun | 357 | CNN_20090906_160000_State_of_the_Union_With_John_King,CNN_20090712_130000_State_of_the_Union_With_John_King |
| 2009-Thu | 283 | CNN_20090716_190000_CNN_Newsroom,CNN_20091203_070000_Anderson_Cooper_360 |
| 2009-Tue | 272 | CNN_20090908_230000_Lou_Dobbs_Tonight,CNN_20091222_100000_CNN_Newsroom |
| 2009-Wed | 278 | CNN_20091216_180000_CNN_Newsroom,CNN_20091230_070000_Anderson_Cooper_360 |
| 2010-Fri | 659 | CNN_20100924_060000_Anderson_Cooper_360,CNN_20100327_010000_Larry_King_Live |
| 2010-Mon | 680 | CNN_20100510_130000_CNN_Newsroom,CNN_20100216_020000_Larry_King_Live |
| 2010-Sat | 1024 | CNN_20100801_010000_Larry_King_Live,CNN_20100807_210000_CNN_Newsroom |
| 2010-Sun | 1036 | CNN_20100214_123000_Sanjay_Gupta_MD,CNN_20100117_123000_Sanjay_Gupta_MD |
| 2010-Thu | 700 | CNN_20100909_150000_CNN_Newsroom,CNN_20100218_140000_CNN_Newsroom |
| 2010-Tue | 688 | CNN_20100316_040000_Larry_King_Live,CNN_20100831_170000_CNN_Newsroom |
| 2010-Wed | 684 | CNN_20100114_003000,CNN_20101027_040000_Larry_King_Live |
| 2011-Fri | 1370 | CNN_20111230_160000_CNN_Newsroom,CNN_20111223_110000_American_Morning |
| 2011-Mon | 1349 | CNN_20111114_060000_CNN_Newsroom,CNN_20110523_230000_John_King_USA |
| 2011-Sat | 1934 | CNNW_20111211_070000_A_Dinner_with_Kings,CNNW_20110910_080000_World_Business_Today |
| 2011-Sun | 1848 | CNNW_20111219_070000_Black_in_America,CNN_20110710_160000_State_of_the_Union |
| 2011-Thu | 1385 | CNN_20111103_060000_Anderson_Cooper_360,CNN_20110825_230000_John_King_USA |
| 2011-Tue | 1423 | CNNW_20111116_020000_Piers_Morgan_Tonight,CNN_20110216_000000_John_King_USA |
| 2011-Wed | 1407 | CNN_20110907_040000_Piers_Morgan_Tonight,CNN_20110601_230000_John_King_USA |
| 2012-Fri | 1726 | CNN_20120803_170000_CNN_Newsroom,CNN_20121109_050000_Piers_Morgan_Tonight |
| 2012-Mon | 1688 | CNNW_20120306_010000_Anderson_Cooper_360,CNN_20120522_020000_Anderson_Cooper_360 |
| 2012-Sat | 2343 | CNN_20121103_080000_Anderson_Cooper_360,CNNW_20120212_000000_Am.s_Choice |
| 2012-Sun | 2411 | CNNW_20120101_090000_Piers_Morgan_Tonight,CNN_20120115_140000_State_of_the_Union |
| 2012-Thu | 1719 | CNNW_20120927_190000_CNN_Newsroom,CNNW_20120329_130000_CNN_Newsroom |
| 2012-Tue | 1669 | CNN_20120425_000000_Anderson_Cooper_360,CNN_20120124_180000_CNN_Newsroom |
| 2012-Wed | 1705 | CNN_20120905_190000_CNN_Newsroom,CNNW_20121226_190000_CNN_Newsroom |
| 2013-Fri | 1243 | CNNW_20130524_080000_Anderson_Cooper_360,CNNW_20130308_170000_Around_the_World |
| 2013-Mon | 1206 | CNNW_20130729_210000_The_Situation_Room,CNNW_20131210_020000_Piers_Morgan_Live |
| 2013-Sat | 1444 | CNNW_20130706_180000_Your_Money,CNNW_20131214_190000_Your_Money |
| 2013-Sun | 1522 | CNNW_20131229_100000_Anderson_Cooper_Special_Report,CNNW_20130609_160000_State_of_the_Union |
| 2013-Thu | 1220 | CNN_20131003_040000_Piers_Morgan_Live,CNNW_20131024_200000_The_Lead_With_Jake_Tapper |
| 2013-Tue | 1230 | CNNW_20130605_000000_Anderson_Cooper_360,CNNW_20130219_100000_EarlyStart |
| 2013-Wed | 1185 | CNNW_20131204_110000_New_Day,CNNW_20131120_110000_New_Day |
| 2014-Fri | 1154 | CNNW_20140919_100000_New_Day,CNNW_20140607_030000_Anthony_Bourdain_Parts_Unknown |
| 2014-Mon | 1095 | CNNW_20141125_060000_CNNI_Simulcast,CNNW_20141117_200000_CNN_Newsroom_With_Brooke_Baldwin |
| 2014-Sat | 1071 | CNNW_20140105_070000_Presumed,CNNW_20140614_070000_Forensic_Files |
| 2014-Sun | 1296 | CNNW_20140811_030000_The_Hunt_With_John_Walsh,CNNW_20140728_050000_Death_Row_Stories |
| 2014-Thu | 1086 | CNNW_20140724_140000_CNN_Newsroom_With_Carol_Costello,CNNW_20140109_110000_New_Day |
| 2014-Tue | 1104 | CNNW_20140527_100000_New_Day,CNNW_20140603_080000_Early_Start_With_John_Berman_and_Christine_Romans |
| 2014-Wed | 1131 | CNNW_20140604_200000_The_Lead_With_Jake_Tapper,CNNW_20140813_080000_Early_Start_With_John_Berman_and_Christine_Romans |
| 2015-Fri | 1043 | CNNW_20151023_080000_Early_Start_With_John_Berman_and_Christine_Romans,CNNW_20150410_150000_At_This_Hour_With_Berman_and_Bolduan |
| 2015-Mon | 1004 | CNNW_20150303_040000_Anderson_Cooper_360,CNNW_20150119_220000_The_Situation_Room |
| 2015-Sat | 1138 | CNNW_20150118_063000_Forensic_Files,CNNW_20150711_210000_CNN_Newsroom_With_Poppy_Harlow |
| 2015-Sun | 1258 | CNNW_20150208_160000_Reliable_Sources,CNNW_20151019_050000_Somebodys_Gotta_Do_It_With_Mike_Rowe |
| 2015-Thu | 1048 | CNNW_20151105_170000_Legal_View_With_Ashleigh_Banfield,CNNW_20151009_050000_CNN_Newsroom_Live |
| 2015-Tue | 1020 | CNNW_20150915_080000_Early_Start_With_John_Berman_and_Christine_Romans,CNNW_20150512_170000_Wolf |
| 2015-Wed | 1033 | CNNW_20150722_080000_Early_Start_With_John_Berman_and_Christine_Romans,CNNW_20151209_100000_Early_Start_With_John_Berman_and_Christine_Romans |
| 2016-Fri | 1066 | CNNW_20161224_050000_This_Is_Life_With_Lisa_Ling,CNNW_20160219_220000_Situation_Room_With_Wolf_Blitzer |
| 2016-Mon | 1016 | CNNW_20160425_080000_Early_Start_With_John_Berman_and_Christine_Romans,CNNW_20161129_030000_CNN_Tonight_With_Don_Lemon |
| 2016-Sat | 1147 | CNNW_20160528_230000_Why_They_Hate_Us,CNNW_20160917_140000_CNN_Newsroom_With_Fredricka_Whitfield |
| 2016-Sun | 1167 | CNNW_20160703_180000_CNN_Newsroom_With_Fredricka_Whitfield,CNNW_20160814_230000_CNN_Newsroom_With_Poppy_Harlow |
| 2016-Thu | 1026 | CNNW_20160526_160000_Legal_View_With_Ashleigh_Banfield,CNNW_20160715_000000_Anderson_Cooper_360 |
| 2016-Tue | 1053 | CNNW_20160119_080000_CNN_Newsroom_Live,CNNW_20160531_100000_New_Day |
| 2016-Wed | 1042 | CNNW_20160203_210000_The_Lead_With_Jake_Tapper,CNNW_20161208_020000_The_Legacy_of_Barack_Obama |
| 2017-Fri | 1240 | CNNW_20171111_070000_CNN_Tonight_With_Don_Lemon,CNNW_20171021_040000_Anderson_Cooper_360 |
| 2017-Mon | 1260 | CNNW_20170821_190000_CNN_Newsroom_With_Brooke_Baldwin,CNNW_20170515_220000_Situation_Room_With_Wolf_Blitzer |
| 2017-Sat | 1274 | CNNW_20171118_120000_New_Day_Saturday,CNNW_20170128_080000_CNN_Newsroom_Live |
| 2017-Sun | 1248 | CNNW_20170611_090000_CNN_Newsroom_Live,CNNW_20170709_100000_New_Day_Sunday |
| 2017-Thu | 1262 | CNNW_20171123_220000_Anthony_Bourdain_Parts_Unknown,CNNW_20170302_210000_The_Lead_With_Jake_Tapper |
| 2017-Tue | 1264 | CNNW_20170823_030000_CNN_Tonight_With_Don_Lemon,CNNW_20170606_210000_Situation_Room_With_Wolf_Blitzer |
| 2017-Wed | 1265 | CNNW_20170405_170000_Wolf,CNNW_20170720_060000_CNN_Tonight_With_Don_Lemon |
| 2018-Fri | 1242 | CNNW_20180714_050000_The_History_of_Comedy,CNNW_20180720_095900_New_Day_With_Alisyn_Camerota_and_John_Berman |
| 2018-Mon | 1259 | CNNW_20181022_070000_CNN_Newsroom_Live,CNNW_20181224_220000_Pope_The_Most_Powerful_Man_in_History |
| 2018-Sat | 1221 | CNNW_20180908_080000_CNN_Newsroom_Live,CNNW_20181103_080000_CNN_Newsroom_Live |
| 2018-Sun | 1229 | CNNW_20181209_140000_State_of_the_Union_With_Jake_Tapper,CNNW_20180409_000000_American_Dynasties_The_Kennedys |
| 2018-Thu | 1244 | CNNW_20180104_200000_CNN_Newsroom_With_Brooke_Baldwin,CNNW_20180823_230000_Erin_Burnett_OutFront |
| 2018-Tue | 1244 | CNNW_20180919_000000_Anderson_Cooper_360,CNNW_20180109_160000_At_This_Hour_With_Kate_Bolduan |
| 2018-Wed | 1245 | CNNW_20181114_160000_At_This_Hour_With_Kate_Bolduan,CNNW_20180524_040000_Anderson_Cooper_360 |
| 2019-Fri | 1243 | CNNW_20190223_010000_Anderson_Cooper_360,CNNW_20191129_180000_CNN_Right_Now_With_Brianna_Keilar |
| 2019-Mon | 1248 | CNNW_20190408_190000_CNN_Newsroom_With_Brooke_Baldwin,CNNW_20190910_040000_Anderson_Cooper_360 |
| 2019-Sat | 1202 | CNNW_20191102_110000_New_Day_Weekend_With_Victor_Blackwell_and_Christi_Paul,CNNW_20190210_050000_The_Eighties |
| 2019-Sun | 1215 | CNNW_20190929_130000_State_of_the_Union_With_Jake_Tapper,CNNW_20190623_120000_Inside_Politics |
| 2019-Thu | 1235 | CNNW_20191011_060000_CNN_Newsroom_Live,CNNW_20190620_130000_CNN_Newsroom_with_Poppy_Harlow_and_Jim_Sciutto |
| 2019-Tue | 1247 | CNNW_20190212_100000_Early_Start_with_Christine_Romans_and_Dave_Briggs,CNNW_20191204_000000_Erin_Burnett_OutFront |
| 2019-Wed | 1228 | CNNW_20190904_234000_The_Lead_With_Jake_Tapper,CNNW_20191030_120000_New_Day_With_Alisyn_Camerota_and_John_Berman |
| 2020-Fri | 1215 | CNNW_20201113_210000_The_Lead_With_Jake_Tapper,CNNW_20201002_070000_CNN_Tonight_with_Don_Lemon |
| 2020-Mon | 1221 | CNNW_20200831_200000_The_Lead_With_Jake_Tapper,CNNW_20201013_010000_Cuomo_Prime_Time |
| 2020-Sat | 1168 | CNNW_20200801_090000_CNN_Newsroom_Live,CNNW_20200530_180000_CNN_Newsroom |
| 2020-Sun | 1222 | CNNW_20201207_040000_State_of_the_Union_With_Jake_Tapper,CNNW_20200322_150000_Reliable_Sources_with_Brian_Stelter |
| 2020-Thu | 1203 | CNNW_20200716_230000_Erin_Burnett_OutFront,CNNW_20200626_040000_Coronavirus_Facts_and_Fears_A_CNN_Global_Town_Hall |
| 2020-Tue | 1175 | CNNW_20201229_105900_New_Day_With_Alisyn_Camerota_and_John_Berman,CNNW_20201216_060000_Cuomo_Prime_Time |
| 2020-Wed | 1214 | CNNW_20200318_200000_The_Lead_With_Jake_Tapper,CNNW_20200521_000000_Anderson_Cooper_360 |
| 2021-Fri | 1247 | CNNW_20210219_190000_CNN_Newsroom_With_Brianna_Keilar,CNNW_20211105_180000_CNN_Newsroom_With_Alisyn_Camerota_and_Victor_Blackwell |
| 2021-Mon | 1234 | CNNW_20210316_040000_Anderson_Cooper_360,CNNW_20210830_200000_The_Lead_With_Jake_Tapper |
| 2021-Sat | 1213 | CNNW_20210327_200000_CNN_Newsroom_With_Ana_Cabrera,CNNW_20211114_040000_Diana |
| 2021-Sun | 1216 | CNNW_20211003_160000_State_of_the_Union_with_Jake_Tapper_and_Dana_Bash,CNNW_20210801_160000_State_of_the_Union_with_Jake_Tapper_and_Dana_Bash |
| 2021-Thu | 1233 | CNNW_20210429_080000_CNN_Newsroom_Live,CNNW_20211008_020000_Don_Lemon_Tonight |
| 2021-Tue | 1211 | CNNW_20210615_190000_CNN_Newsroom_With_Alisyn_Camerota_and_Victor_Blackwell,CNNW_20210526_040000_Anderson_Cooper_360 |
| 2021-Wed | 1208 | CNNW_20210304_050000_Anderson_Cooper_360,CNNW_20210520_050000_Cuomo_Prime_Time |
| 2022-Fri | 1244 | CNNW_20220325_110000_New_Day_With_John_Berman_and_Brianna_Keilar,CNNW_20220701_180000_CNN_Newsroom_With_Alisyn_Camerota_and_Victor_Blackwell |
| 2022-Mon | 1226 | CNNW_20220110_140000_CNN_Newsroom_With_Poppy_Harlow_and_Jim_Sciutto,CNNW_20220207_220000_The_Lead_With_Jake_Tapper |
| 2022-Sat | 1225 | CNNW_20220423_230000_CNN_Newsroom_With_Pamela_Brown,CNNW_20220219_120000_New_Day_Weekend_With_Christi_Paul_and_Boris_Sanchez |
| 2022-Sun | 1227 | CNNW_20220925_160000_State_of_the_Union_With_Jake_Tapper_and_Dana_Bash,CNNW_20220814_140000_Fareed_Zakaria_GPS |
| 2022-Thu | 1223 | CNNW_20221020_170000_CNN_Newsroom_With_Ana_Cabrera,CNNW_20220121_050000_Anderson_Cooper_360 |
| 2022-Tue | 1200 | CNNW_20220803_050000_CNN_Tonight,CNNW_20221019_050000_CNN_Tonight |
| 2022-Wed | 1220 | CNNW_20220331_050000_CNN_Newsroom_Live,CNNW_20220413_090000_Early_Start_With_Christine_Romans_and_Laura_Jarrett |
| 2023-Fri | 1245 | CNNW_20230113_150000_CNN_Newsroom,CNNW_20230630_190000_CNN_News_Central |
| 2023-Mon | 1236 | CNNW_20231030_170000_CNN_News_Central,CNNW_20230227_110000_CNN_This_Morning |
| 2023-Sat | 1192 | CNNW_20230826_070000_CNN_Tonight,CNNW_20230114_130000_CNN_This_Morning_Weekend |
| 2023-Sun | 1232 | CNNW_20230806_180000_CNN_Newsroom_With_Fredricka_Whitfield,CNNW_20230423_160000_State_of_the_Union_With_Jake_Tapper_and_Dana_Bash |
| 2023-Thu | 1243 | CNNW_20231026_180000_CNN_News_Central,CNNW_20230615_170000_CNN_News_Central |
| 2023-Tue | 1220 | CNNW_20230822_210000_The_Situation_Room_With_Wolf_Blitzer,CNNW_20230905_140000_CNN_News_Central |
| 2023-Wed | 1230 | CNNW_20230830_200000_The_Lead_With_Jake_Tapper,CNNW_20230118_150000_CNN_Newsroom |
| 2024-Fri | 815 | CNNW_20240726_120000_CNN_News_Central,CNNW_20240517_220000_The_Situation_Room_With_Wolf_Blitzer |
| 2024-Mon | 792 | CNNW_20240506_120000_CNN_News_Central,CNNW_20240820_040000_CNN_Democratic_National_Convention |
| 2024-Sat | 815 | CNNW_20240609_020000_Secrets__Spies_A_Nuclear_Game,CNNW_20240309_160000_The_Amanpour_Hour |
| 2024-Sun | 809 | CNNW_20240721_210000_CNN_Newsroom,CNNW_20240811_180000_CNN_Newsroom_With_Fredricka_Whitfield |
| 2024-Thu | 781 | CNNW_20240503_040000_Anderson_Cooper_360,CNNW_20240725_140000_CNN_Newsroom_With_Jim_Acosta |
| 2024-Tue | 766 | CNNW_20240326_210000_The_Situation_Room_With_Wolf_Blitzer,CNNW_20240827_140000_CNN_Newsroom_With_Jim_Acosta |
| 2024-Wed | 806 | CNNW_20240814_130000_CNN_News_Central,CNNW_20240529_120000_CNN_News_Central |