Skip to content

Instantly share code, notes, and snippets.

@qi-qi

qi-qi/azure-sql-dw-sql-snippet.sql

Last active May 28, 2019 13:17
Show Gist options
  • Save qi-qi/04d61400e9d18ea0dd1a52be45aa60d7 to your computer and use it in GitHub Desktop.
Save qi-qi/04d61400e9d18ea0dd1a52be45aa60d7 to your computer and use it in GitHub Desktop.
Download ZIP
Azure SQL Datawarehouse
Raw
azure-sql-dw-sql-snippet.sql
--admin
SELECT *
FROM sys.dm_pdw_exec_requests
-- WHERE status not in ('Completed','Failed','Cancelled')
-- AND session_id <> session_id()
ORDER BY submit_time DESC;
EXEC sp_addrolemember 'xlargerc', 'dbo';
SELECT DP1.name AS DatabaseRoleName,
isnull(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;
SELECT CURRENT_USER;
EXEC sp_droprolemember 'xlargerc', 'looker';
EXEC sp_helptext prc_episodes_remove_duplicates;
SELECT OBJECT_DEFINITION(OBJECT_ID(N'prc_episodes_remove_duplicates'))
SELECT *
INTO [dbo].[Campaigns_backup_20190509]
FROM [dbo].[Campaigns]
SELECT *
INTO [dbo].[Advertisers_backup_20190509]
FROM [dbo].[Advertisers]
SELECT *
INTO [dbo].[Creatives_backup_20190509]
FROM [dbo].[Creatives]
SELECT *
INTO [dbo].[Estimates_backup_20190509]
FROM [dbo].[Estimates]
SELECT *
INTO [dbo].[Flights_backup_20190509]
FROM [dbo].[Flights]
select count(*)
from dbo.Advertisers;
select id, idx
from (SELECT id, row_number() OVER (PARTITION BY id ORDER BY Modified DESC) as idx from dbo.Advertisers) as t1
where t1.idx > 1
select *
from advertisers
where id + CONVERT(VARCHAR(24), modified) in (select s.id + CONVERT(VARCHAR(24), s.modified) as combinedKey
from advertisers s
where s.modified not in (select max(sh.modified)
from advertisers as sh
where sh.id = s.id))
delete
from Advertisers
where (SELECT id, row_number() OVER (PARTITION BY id ORDER BY Modified DESC) as idx from dbo.Advertisers t1)
-- read-only
select top 10 *
from dbo.DownloadStats
where len(ParentChannel) > 0;
select top 10 *
from dbo.DownloadStats
where len(ParentChannel) > 0;
select top 300 *
from dbo.AdImpressionStats;
select Country, count(distinct id)
from dbo.AdImpressionStats
where Date >= '2018-09-01'
and Date <= '2018-09-30'
group by Country
order by 2 desc;
select count(*) as cnt, ChannelName
from dbo.DownloadStats
where Country = 'US'
group by ChannelName
order by 1 desc;
select count(*) as cnt, ChannelName, ChannelId
from dbo.DownloadStats
where Country = 'US'
group by ChannelName, ChannelId
order by 1 desc;
select count(distinct ClientId) as cnt, Useragent
from dbo.DownloadStats
where Country = 'US'
and ChannelName = 'My Dad Wrote A Porno'
group by Useragent
order by 1 desc;
select count(*) as cnt, cast(Date as date) as Date, Hour
from dbo.AdImpressionStats
group by Date, Hour
order by 2 desc, 3 desc
select count(*) as cnt, concat(Date, '-', RIGHT('00' + CONVERT(VARCHAR, Hour), 2)) as DateHour
from dbo.AdImpressionStats
group by Date, Hour
order by 2 desc;
select sum(case when Referer like '%alexa.com%' then 1 else 0 end) as alexa,
sum(case when Referer not like '%alexa.com%' then 1 else 0 end) as nonalexa,
count(*) as tot,
(sum(case when Referer like '%alexa.com%' then 1 else 0 end) * 100 / count(*)) as perc,
min(date) as first_seen,
max(date) as last_seen
from dbo.DownloadStats
where ChannelUrl = 'sipssudsandsmokes'
and date > '2018-01-01'
select Date, Hour, count(*)
from dbo.DownloadStats
where Date >= '2018-11-06'
and ClientType in ('rss', 'embed')
group by Date, Hour
order by 1, 2;
select top 100 *
from dbo.AdImpressionStats
where FlightName <> '';
select top 100 *
from dbo.Flights;
select top 100 *
from dbo.Creatives
where AdId = '5314';
select max(Modified)
from dbo.Episodes
select max(PublishingDate)
from dbo.Acasts
where ChannelId = 'bf4e5114-fe02-4088-9a07-c1b81a3c615a'
select top 100 *
from dbo.Campaigns
where id = 'd201fbfa-7ea6-522d-da72-4fba8391fdde'
select top 100 *
from AdImpressionStats
select Date, DeviceOs, count(*) as cnt
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and DeviceOs = 'ios'
and Date >= '2018-08-01'
and Date <= '2018-11-01'
group by Date, DeviceOs
order by 1;
select Date, count(distinct Ip + Useragent) as cnt
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date >= '2018-08-01'
and Date <= '2018-10-31'
and DeviceOs = 'iOS'
group by Date
having count(*) > 100
order by 1, 2
select Date,
Hour,
Ip,
Useragent,
ClientType,
AcastId,
DeviceOs,
count(*) as cnt
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date >= '2018-09-30'
and Date <= '2018-10-02'
group by Date, Hour, Ip, Useragent, ClientType, AcastId, DeviceOs
order by Date, Hour, AcastId, cnt desc;
select Date, Hour, Ip, Useragent, ClientType, DeviceOs, count(*) as cnt
from DownloadStats
where AcastId = '70e7f53b-0495-4dac-b2a1-daa4514ea7cb'
and Date >= '2018-09-30'
and Date <= '2018-10-02'
group by Date, Hour, Ip, Useragent, ClientType, DeviceOs
order by Date, Hour, cnt desc;
select Date,
Hour,
Ip,
Useragent,
ClientType,
AcastName,
DeviceOs,
count(*) as cnt
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date >= '2018-09-30'
and Date <= '2018-10-02'
group by Date, Hour, Ip, Useragent, ClientType, AcastName, DeviceOs
order by cnt desc;
select Date, Hour, count(*)
from dbo.DownloadStats
where Date >= '2018-09-25'
and Date <= '2018-10-05'
and ClientType in ('rss', 'embed')
and ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
group by Date, Hour
order by 1, 2;
select Date, count(*) as cnt, Useragent, DeviceOs, DeviceType
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date >= '2018-09-29'
and Date <= '2018-10-02'
group by Date, Useragent, DeviceOs, DeviceType
having count(*) > 100
order by Date asc, cnt desc
select Date, count(*) as cnt, Useragent, DeviceOs, AcastName
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date in ('2018-09-27', '2018-10-01', '2018-10-22')
group by Date, Useragent, DeviceOs, AcastName
having count(*) > 500
order by Date asc, cnt desc
select Date, Useragent, max(t1.cnt)
from (select Date, Useragent, count(*) as cnt
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date >= '2018-09-01'
and Date <= '2018-10-31'
group by Date, Useragent
order by 1, 2) t1
group by t1.Date, t1.Useragent
order by 1, 2;
-- This is a wrong one but happen to discover clear Text IP
select Date, Ip, count(ip) as cnt_ip, count(distinct ip) as cnt_distinct_ip
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date = '2018-10-01'
and Useragent like 'AppleCoreMedia/1.0.0.15G77%'
or Useragent like 'AppleCoreMedia/1.0.0.16A366%'
group by Date, Ip
order by 1, cnt_ip desc;
-- This is a wrong one but happen to discover clear Text IP
select Date, Ip, count(ip) as cnt_ip, count(distinct ip) as cnt_distinct_ip
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date = '2018-10-01'
and (Useragent like 'AppleCoreMedia/1.0.0.15G77%' or Useragent like 'AppleCoreMedia/1.0.0.16A366%')
group by Date, Ip
order by 1, cnt_ip desc;
select Date, count(*) as cnt
from DownloadStats
where Ip like '[0-9]%'
and Ip like '%[0-9]'
group by Date, Ip
order by Date desc, cnt desc;
select Date, Ip, count(*) as cnt
from AdImpressionStats
where Ip like '[0-9]%'
and Ip like '%[0-9]'
group by Date, Ip
order by Date desc, cnt desc;
select count(distinct IP + Useragent), Date
from DownloadStats
where Date >= '2018-08-01'
and Date <= '2018-10-31'
and ChannelId in (select distinct id from Shows where NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8')
group by Date
order by Date;
select distinct id
from Shows
where NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8';
select top 100 *
from DownloadStats
where ChannelId = 'a14ac9f7-803d-4620-bf3f-c17bc8ddb2ec';
select date,
count(distinct (Ip + Useragent)) as count_distinct,
count(*) as count_total,
CONCAT(count(distinct (Ip + Useragent)) / cast(count(*) as float) * 100, '%') as distinct_percentage
from AdImpressionStats
where Date >= '2018-10-01'
group by Date
order by Date;
select concat(DATE, '-', Hour) as date_hour, count(*) as cnt
from AdImpressionStats
where Date >= '2018-11-15'
group by Date, Hour
order by Date, Hour;
select DATE, count(id) as cnt
from DownloadStats
where Date >= '2018-11-15'
group by Date
order by Date;
select concat(DATE, '-', Hour) as date_hour, count(*) as cnt
from DownloadStats
where Date >= '2018-11-15'
group by Date, Hour
order by Date, Hour;
select concat(DATE, '-', Hour) as date_hour, count(Date) as cnt
from AdImpressionStats
where Date >= '2018-11-15'
group by Date, Hour
order by Date, Hour;
select concat(DATE, '-', Hour) as date_hour, count(Date) as cnt
from DownloadStats
where Date >= '2018-11-15'
group by Date, Hour
order by Date, Hour;
select sum(case when ImpressionUrl like '%listenerId%' then 1 else 0 end) as count_has_listenerIDs,
count(*) as count_all_ads,
CONCAT(sum(case when ImpressionUrl like '%listenerId%' then 1 else 0 end) / cast(count(*) as float) * 100,
'%') as percentage
from AdImpressionStats
where Date >= '2018-11-01'
group by Date
order by Date;
select max(Timestamp)
from AdImpressionStats;
select Country, Date, count(*) as cnt
from AdImpressionStats
where Date = '2018-11-12'
group by Country, Date
order by 3 desc, 1, 2;
select count(distinct Ip + Useragent)
from AdImpressionStats
where Date = '2018-11-12';
select count(*)
from AdImpressionStats
where Date = '2018-11-12';
select Date, Hour, Timestamp, Week, Year, AcastId
from DownloadStats
where Date = '2018-11-15'
and (SourceBucket like '%2018/11/20/%');
select ImpressionUrl
from AdImpressionStats
where ImpressionUrl like
'%reqType=AdsSetup&protocolVersion=2.0-compliant&zoneId=[%7Bz:188,n:1%7D]&companionZones=190&cb=4a33b1fc-b825-4772-be21-e43c33342cba&aw_0_1st.contentcategory=comedy&maxAds=1&aw_0_1st.device=podcasts&aw_0_1st.devicetype=other&aw_0_1st.showcountry=GB&aw_0_1st.os=other&aw_0_1st.platform=rss&aw_0_1st.rating=no&aw_0_1st.salesvertical=[%22comedy%22,%22filmentertainment%22,%22curiousthinkers%22,%22lifestyle%22,%22women%22,%22parentingfamilies%22,%22women2544%22,%22women45%22]&aw_0_1st.show=standardissuespodcast&aw_0_1st.owneroffice=london&aw_0_azn.brandName=acast&aw_0_awz.companionRequest=true&aw_0_awz.g=%5B%22m%22%5D&aw_0_azn.gender=%5B%22m%22%5D&aw_0_azn.pname=%5B%22Acast%20General%20Inventory%22%5D&aw_0_req.gdprConsentGiven=false&aw_0_awz.accuracy=100&aw_0_azn.brandDomain=http%3A%2F%2Fwww.acast.com&aw_0_awz.a=%5B%225564%22%5D&aw_0_azn.zposition=%5B%22mid-roll%22%5D&aw_0_awz.poi=%5B%5D&aw_0_azn.age=%5B%225564%22%5D%'
and DATE = '2018-11-12';
select count(distinct Ip + Useragent)
from DownloadStats
where Date = '2018-11-12';
select Date, count(id) as cnt
from DownloadStats
group by Date
order by Date;
select top 100 *
from SessionStats
select top 100 Birthday
from UserData
select UserId, DATEDIFF(YEAR, Cast(Birthday as DATE), GETDATE()) as age, Gender
from UserData;
select ChannelUrl, t2.age, t2.Gender
from DownloadStats t1
left outer join (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData) t2
on t1.UserId = t2.UserId
where ClientType = 'app'
and ChannelUrl in
('sparpodcast')
-- ('sparpodcast', 'sakerstil', 'rebeccaochvanessa', 'alltduvelatveta', 'dellasport', 'vadblirdetformord', 'mordmotmord', 'pillowtalk', 'mittilivet', 'mathildaochandrea', 'lundh', 'markochjonas', 'sportbladetsplp', 'lillelordag', 'tvasystrarenpodd', 'studioallsvenskan', 'lakarpodden')
and date = '2018-11-11'
select (select DATEDIFF(YEAR, Birthday, GETDATE()) as age from UserData) t2
group by age
select age, count(*) as counting
from (select DATEDIFF(YEAR, Birthday, GETDATE()) as age from UserData) t
group by age
order by age
-- select top 100 ChannelUrl, t2.age, t2.Gender
-- from DownloadStats t1
-- left outer join (select UserId, DATEDIFF(YEAR, Cast(Birthday as DATE), GETDATE()) as age, Gender
-- from UserData where DATEDIFF(YEAR, Cast(Birthday as DATE), GETDATE()) < 100) t2 on t1.UserId = t2.UserId
-- where ClientType = 'app'
-- and ChannelUrl in ('sparpodcast')
-- -- ('sparpodcast', 'sakerstil', 'rebeccaochvanessa', 'alltduvelatveta', 'dellasport', 'vadblirdetformord', 'mordmotmord', 'pillowtalk', 'mittilivet', 'mathildaochandrea', 'lundh', 'markochjonas', 'sportbladetsplp', 'lillelordag', 'tvasystrarenpodd', 'studioallsvenskan', 'lakarpodden')
-- and date >= '2018-11-11'
select ChannelUrl,
t2.age,
t2.Gender,
sum(case when t2.age > 0 and t2.age < 18 then 1 else 0 end) as age_0_18_sum,
sum(case when t2.age >= 18 and t2.age < 24 then 1 else 0 end) as age_18_24_sum,
sum(case when t2.age >= 25 and t2.age < 34 then 1 else 0 end) as age_25_34_sum,
sum(case when t2.age >= 35 and t2.age < 44 then 1 else 0 end) as age_35_44_sum,
sum(case when t2.age >= 45 and t2.age < 85 then 1 else 0 end) as age_45_85_sum,
sum(case when t2.Gender = 'male' then 1 else 0 end) as male_sum,
sum(case when t2.Gender = 'female' then 1 else 0 end) as female_sum,
sum(case when t2.Gender = '' then 1 else 0 end) as empty_gender_sum
from DownloadStats t1
left outer join (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData) t2
on t1.UserId = t2.UserId
where ClientType = 'app'
and ChannelUrl in
('sparpodcast')
-- ('sparpodcast', 'sakerstil', 'rebeccaochvanessa', 'alltduvelatveta', 'dellasport', 'vadblirdetformord', 'mordmotmord', 'pillowtalk', 'mittilivet', 'mathildaochandrea', 'lundh', 'markochjonas', 'sportbladetsplp', 'lillelordag', 'tvasystrarenpodd', 'studioallsvenskan', 'lakarpodden')
and date = '2018-11-11'
group by ChannelUrl, t2.age, t2.Gender
order by age
select count(id) as total_count,
sum(case when t2.age > 0 and t2.age < 18 then 1 else 0 end) as age_0_18_sum,
sum(case when t2.age >= 18 and t2.age < 24 then 1 else 0 end) as age_18_24_sum,
sum(case when t2.age >= 25 and t2.age < 34 then 1 else 0 end) as age_25_34_sum,
sum(case when t2.age >= 35 and t2.age < 44 then 1 else 0 end) as age_35_44_sum,
sum(case when t2.age >= 45 and t2.age < 85 then 1 else 0 end) as age_45_85_sum,
sum(case when t2.age >= 85 then 1 else 0 end) as age_85_plus_sum,
sum(case when t2.Gender = 'male' then 1 else 0 end) as male_sum,
sum(case when t2.Gender = 'female' then 1 else 0 end) as female_sum,
sum(case when t2.Gender = '' then 1 else 0 end) as empty_gender_sum
from DownloadStats t1
left outer join (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData) t2
on t1.UserId = t2.UserId
where ClientType = 'app'
and ChannelUrl in
('aliceochbianca')
-- ('sparpodcast', 'sakerstil', 'rebeccaochvanessa', 'alltduvelatveta', 'dellasport', 'vadblirdetformord', 'mordmotmord', 'pillowtalk', 'mittilivet', 'mathildaochandrea', 'lundh', 'markochjonas', 'sportbladetsplp', 'lillelordag', 'tvasystrarenpodd', 'studioallsvenskan', 'lakarpodden')
and year = 2018
select top 100 Country
from DownloadStats
where ClientType = 'app'
and year = 2018;
select ChannelUrl, t2.age, t2.Gender
from DownloadStats t1
left outer join (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData) t2
on t1.UserId = t2.UserId
where ClientType = 'app'
and ChannelUrl in
('alice')
-- ('sparpodcast', 'sakerstil', 'rebeccaochvanessa', 'alltduvelatveta', 'dellasport', 'vadblirdetformord', 'mordmotmord', 'pillowtalk', 'mittilivet', 'mathildaochandrea', 'lundh', 'markochjonas', 'sportbladetsplp', 'lillelordag', 'tvasystrarenpodd', 'studioallsvenskan', 'lakarpodden')
and year = 2018
group by ChannelUrl,
t2.age,
t2.Gender
order by ChannelUrl, t2.age;
select max(ChannelUrl) as channel_url,
sum(case when t2.age > 0 and t2.age < 18 then 1 else 0 end) as age_0_18_sum,
sum(case when t2.age >= 18 and t2.age < 24 then 1 else 0 end) as age_18_24_sum,
sum(case when t2.age >= 25 and t2.age < 34 then 1 else 0 end) as age_25_34_sum,
sum(case when t2.age >= 35 and t2.age < 44 then 1 else 0 end) as age_35_44_sum,
sum(case when t2.age >= 45 and t2.age < 85 then 1 else 0 end) as age_45_85_sum,
sum(case when t2.age >= 85 then 1 else 0 end) as age_85_plus_sum,
sum(case when t2.Gender = 'male' then 1 else 0 end) as male_sum,
sum(case when t2.Gender = 'female' then 1 else 0 end) as female_sum,
sum(case
when t2.Gender <> 'male' and t2.Gender <> 'female' then 1
else 0 end) as other_gender_sum
from DownloadStats t1
left outer join (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData) t2
on t1.UserId = t2.UserId
where ClientType = 'app'
and ChannelUrl in
('aliceochbianca')
and Country = 'SE'
and Year = 2018;
----- ALL -----
WITH App as (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData)
select ChannelUrl, Year, App.age, App.Gender, count(ChannelUrl) as counting
from DownloadStats t1
left outer join App on t1.UserId = App.UserId
where ClientType = 'app'
group by Year,
App.age,
App.Gender,
ChannelUrl
order by ChannelUrl, Year, App.age, App.Gender;
select Referer, Useragent
from DownloadStats
where ChannelUrl = 'theeconomistasks'
and City = 'Mount Holly'
and Date = '2018-11-04'
select Useragent, count(Useragent) as cnt
from DownloadStats
where ChannelUrl = 'theeconomistasks'
and City = 'Mount Holly'
and Year = 2018
and Month = 10
group by Useragent
order by 2 desc
select Referer, count(Referer) as cnt
from DownloadStats
where ChannelUrl = 'theeconomistasks'
and City = 'Ashburn'
and Year = 2018
and Month = 10
group by Referer
order by 2 desc
select ISP, count(ISP)
from DownloadStats
where ChannelUrl = 'theeconomistasks'
and City = 'Ashburn'
group by ISP
order by 2 desc
select top 100 City, AcastUrl, ChannelUrl
from DownloadStats
where AcastUrl like '%theeconomistmoneytalks%'
or ChannelUrl like '%theeconomistmoneytalks%'
----- ALL + Country-----
WITH App as (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData)
select ChannelUrl, Year, App.age, App.Gender, Country, count(ChannelUrl) as counting
from DownloadStats t1
left outer join App on t1.UserId = App.UserId
where ClientType = 'app'
group by Year,
App.age,
App.Gender,
ChannelUrl,
Country
order by ChannelUrl, Year, App.age, App.Gender, Country;
select count(Gender) as counting, Gender
from UserData
group by Gender;
select Useragent, Country, count(Useragent) as cnt
from DownloadStats
where ChannelId = 'f2cb4af8-bcd9-4baa-9908-3a9dd3d973f4'
and Year = 2018
and Month = 10
group by Useragent, Country
order by cnt desc
select Date, Ip, Useragent, AcastUrl, referer, count(*) as cnt
from DownloadStats
where ChannelId = 'f2cb4af8-bcd9-4baa-9908-3a9dd3d973f4'
and Country = 'DE'
and Year = 2018
and Month = 11
and Ip = 'mNCrytfNoQipM4HwPzYgrw=='
group by IP, Useragent, Date, referer, AcastUrl
order by Date, AcastUrl, cnt desc
select top 100 *
from DownloadStats
where Date = '2018-12-01'
select substring(Useragent, 1, charindex('/', Useragent)) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3) as ua,
count(substring(Useragent, 1, charindex('/', Useragent))) as cnt
from DownloadStats
-- where Date = '2018-12-01'
where Year = 2018
and Month = 11
group by substring(Useragent, 1, charindex('/', Useragent)) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
order by 2 desc
select substring(Useragent, 1, charindex('/', Useragent)) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3) as ua
from DownloadStats
-- where Date = '2018-12-01'
where Year = 2018
and Month = 11
group by substring(Useragent, 1, charindex('/', Useragent)) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
order by 2 desc
with UA as (select DATE,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent not like '%/%' then '(others)'
when Useragent like '(null)/(null) watchOS%'
then '(null)/(null) watchOS'
when Useragent like 'AppleCoreMedia%' then
substring(Useragent, 1, charindex('/', Useragent) - 1) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
else substring(Useragent, 1, charindex('/', Useragent) - 1) end) as ua_parsed
from DownloadStats
where Date <= '2018-12-03')
select UA.Date, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ua_parsed
having count(UA.ua_parsed) > 300
order by UA.Date, count(UA.ua_parsed) desc;
-- v2 --
with UA as (select DATE,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent like '(null)/(null) watchOS/5%'
then '(null)/(null) watchOS 5'
when Useragent like 'AppleCoreMedia%iPhone%'
then 'iPhone OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPad%'
then 'iPad OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%HomePod%'
then 'HomePod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%Apple Watch%'
then 'Apple Watch OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPod%'
then 'iPod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like '%/%' then substring(Useragent, 1, charindex('/', Useragent) - 1)
else '(unknown)' end) as ua_parsed
from DownloadStats
where Date <= '2018-12-03')
select UA.Date, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ua_parsed
-- having count(UA.ua_parsed) > 300
order by UA.Date, count(UA.ua_parsed) desc;
select Date, Useragent, count(Useragent) as counting
from DownloadStats
where Year = 2018
and lower(Useragent) like '%guardian%'
group by Date, Useragent
order by Date, counting desc
select Useragent, count(Useragent) as cnt
from DownloadStats
where Year = 2018
and Month = 12
and (lower(Useragent) like 'podcast/%' or lower(Useragent) like 'podcasts/%')
group by Useragent
order by 2 desc;
select Year, Month, Useragent, count(Useragent) as cnt
from DownloadStats
where Year = 2018
and (lower(Useragent) like 'podcast/%' or lower(Useragent) like 'podcasts/%')
group by Useragent, Year, Month
order by Year desc, Month desc, count(Useragent) desc;
select Year, Month, Useragent, count(Useragent) as cnt
from DownloadStats
where Year = 2018
and (lower(Useragent) like 'appletv%')
group by Useragent, Year, Month
order by Year desc, Month desc, count(Useragent) desc;
with UA as (select DATE,
ChannelUrl,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent not like '%/%' then '(others)'
when Useragent like '(null)/(null) watchOS%'
then '(null)/(null) watchOS'
when Useragent like 'AppleCoreMedia%' then
substring(Useragent, 1, charindex('/', Useragent) - 1) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
else substring(Useragent, 1, charindex('/', Useragent) - 1) end) as ua_parsed
from DownloadStats
where Year = 2018)
select UA.Date, UA.ChannelUrl, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ChannelUrl, UA.ua_parsed
having count(UA.ua_parsed) > 50
order by UA.Date, count(UA.ua_parsed) desc;
-- v3 --
with UA as (select DATE,
ChannelUrl,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent like '(null)/(null) watchOS/5%'
then '(null)/(null) watchOS 5'
when Useragent like 'AppleCoreMedia%iPhone%'
then 'iPhone OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPad%'
then 'iPad OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%HomePod%'
then 'HomePod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%Apple Watch%'
then 'Apple Watch OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPod%'
then 'iPod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like '%/%' then substring(Useragent, 1, charindex('/', Useragent) - 1)
else '(unknown)' end) as ua_parsed
from DownloadStats
where Date <= '2018-12-05')
select UA.Date, UA.ChannelUrl, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ChannelUrl, UA.ua_parsed
having count(UA.ua_parsed) > 30
order by UA.Date, count(UA.ua_parsed) desc;
-- v4 --
with UA as (select DATE,
ChannelUrl,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent like '(null)/(null) watchOS/5%'
then 'Apple (null)/(null) watchOS 5'
when Useragent like 'AppleCoreMedia%iPhone%'
then 'Apple iPhone OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPad%'
then 'Apple iPad OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%HomePod%'
then 'Apple HomePod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%Apple Watch%'
then 'Apple Watch OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPod%'
then 'Apple iPod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like '%/%' then substring(Useragent, 1, charindex('/', Useragent) - 1)
else '(others)' end) as ua_parsed
from DownloadStats
where Date >= '2017-01-01'
and Date <= '2018-12-13'
and ChannelUrl is not null
and ChannelUrl != '')
select UA.Date, UA.ChannelUrl, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ChannelUrl, UA.ua_parsed
order by UA.Date, count(UA.ua_parsed) desc;
select Date, Country, City, Isp, count(Country) as counting
from AdImpressionStats
where ChannelUrl = 'theeconomistasks'
and City = 'Ashburn'
group by Date, City, Country, Isp
order by Date asc;
SELECT year, month, day, clientType, count(clientType) as counting
FROM DownloadStats
where date >= '2018-12-05'
group by clientType, year, month, day
order by year, month, day, clientType
with UA as (select DATE,
(case
when Useragent like '(null)/(null) watchOS/5%'
then '(null)/(null) watchOS 5'
else 'others' end) as ua_parsed
from DownloadStats
where Date >= '2018-06-01')
select UA.Date, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ua_parsed
order by UA.Date, UA.ua_parsed;
select DeviceOs, DeviceOs, DeviceVersion, ClientType, Useragent, count(Useragent) as counting
from DownloadStats
where Date = '2018-12-11'
group by DeviceOs, DeviceOs, DeviceVersion, ClientType, Useragent
order by count(Useragent) desc
select top 100 *
from SessionStats
where EndSecond != 0;
select max(Modified)
from Episodes;
-- v5 --
with t as (select UA.Date,
UA.ChannelUrl,
case
when count(UA.ua_parsed) < 30 then '(others)'
else UA.ua_parsed end as ua_parsed,
count(UA.ua_parsed) as cnt
from (select DATE,
ChannelUrl,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent like '(null)/(null) watchOS/5%'
then 'Apple (null)/(null) watchOS/5'
when Useragent like 'AppleCoreMedia%iPhone%'
then 'Apple iPhone OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPad%'
then 'Apple iPad OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%HomePod%'
then 'Apple HomePod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%Apple Watch%'
then 'Apple Watch OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPod%'
then 'Apple iPod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like '%/%' then substring(Useragent, 1, charindex('/', Useragent) - 1)
else '(others)' end) as ua_parsed
from DownloadStats
where Date <= '2018-12-13'
and ChannelUrl is not null
and ChannelUrl != '') UA
group by UA.Date, UA.ChannelUrl, UA.ua_parsed)
select t.Date, t.ChannelUrl, t.ua_parsed, sum(t.cnt) as total
from t
group by t.Date, t.ChannelUrl, t.ua_parsed
order by t.Date, sum(t.cnt) desc
with UA as (select DATE,
ChannelUrl,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent like '(null)/(null) watchOS/5%'
then 'Apple (null)/(null) watchOS 5'
when Useragent like 'AppleCoreMedia%iPhone%'
then 'Apple iPhone OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPad%'
then 'Apple iPad OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%HomePod%'
then 'Apple HomePod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%Apple Watch%'
then 'Apple Watch OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPod%'
then 'Apple iPod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like '%/%' then substring(Useragent, 1, charindex('/', Useragent) - 1)
else '(others)' end) as ua_parsed
from AdImpressionStats
where Date <= '2018-12-13')
select UA.Date, UA.ChannelUrl, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ChannelUrl, UA.ua_parsed
having count(UA.ua_parsed) > 20
order by UA.Date, count(UA.ua_parsed) desc;
select top 100 *
from Payouts;
select Date, Hour, count(Hour) as cnt
from DownloadStats
where Date >= '2018-12-01'
group by Date, Hour
order by Date, Hour;
select Date, Hour, count(Hour) as cnt
from AdImpressionStats
where Date >= '2018-12-01'
group by Date, Hour
order by Date, Hour;
select cast(Timestamp as DATE), DATEPART(HOUR, Timestamp), count(*) as cnt
from SessionStats
where cast(Timestamp as DATE) >= '2018-12-01'
group by cast(Timestamp as DATE), DATEPART(HOUR, Timestamp)
order by cast(Timestamp as DATE), DATEPART(HOUR, Timestamp);
SELECT download_stats.ClientType AS "download_stats.client_type",
COUNT(*) AS "download_stats.count"
FROM dbo.DownloadStats AS download_stats
GROUP BY download_stats.ClientType
select ClientType, COUNT_BIG(*)
from DownloadStats
group by ClientType
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed', 'Failed', 'Cancelled')
AND session_id <> session_id()
ORDER BY submit_time DESC;
select top 500 *
from Payouts
select top 500 *
from DownloadStats
where Date = '2018-12-17'
and ChannelUrl = 'sheerluxe';
select top 10 *
from dbo.DownloadStats
where len(ParentChannel) > 0;
select top 10 *
from dbo.DownloadStats
where len(ParentChannel) > 0;
select top 300 *
from dbo.AdImpressionStats;
select Country, count(distinct id)
from dbo.AdImpressionStats
where Date >= '2018-09-01'
and Date <= '2018-09-30'
group by Country
order by 2 desc;
select count(*) as cnt, ChannelName
from dbo.DownloadStats
where Country = 'US'
group by ChannelName
order by 1 desc;
select count(*) as cnt, ChannelName, ChannelId
from dbo.DownloadStats
where Country = 'US'
group by ChannelName, ChannelId
order by 1 desc;
select count(distinct ClientId) as cnt, Useragent
from dbo.DownloadStats
where Country = 'US'
and ChannelName = 'My Dad Wrote A Porno'
group by Useragent
order by 1 desc;
select count(*) as cnt, cast(Date as date) as Date, Hour
from dbo.AdImpressionStats
group by Date, Hour
order by 2 desc, 3 desc
select count(*) as cnt, concat(Date, '-', RIGHT('00' + CONVERT(VARCHAR, Hour), 2)) as DateHour
from dbo.AdImpressionStats
group by Date, Hour
order by 2 desc;
select sum(case when Referer like '%alexa.com%' then 1 else 0 end) as alexa,
sum(case when Referer not like '%alexa.com%' then 1 else 0 end) as nonalexa,
count(*) as tot,
(sum(case when Referer like '%alexa.com%' then 1 else 0 end) * 100 / count(*)) as perc,
min(date) as first_seen,
max(date) as last_seen
from dbo.DownloadStats
where ChannelUrl = 'sipssudsandsmokes'
and date > '2018-01-01'
select Date, Hour, count(*)
from dbo.DownloadStats
where Date >= '2018-11-06'
and ClientType in ('rss', 'embed')
group by Date, Hour
order by 1, 2;
select top 100 *
from dbo.AdImpressionStats
where FlightName <> '';
select top 100 *
from dbo.Flights;
select top 100 *
from dbo.Creatives
where AdId = '5314';
select max(Modified)
from dbo.Episodes
select max(PublishingDate)
from dbo.Acasts
where ChannelId = 'bf4e5114-fe02-4088-9a07-c1b81a3c615a'
select top 100 *
from dbo.Campaigns
where id = 'd201fbfa-7ea6-522d-da72-4fba8391fdde'
select top 100 *
from AdImpressionStats
select Date, DeviceOs, count(*) as cnt
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and DeviceOs = 'ios'
and Date >= '2018-08-01'
and Date <= '2018-11-01'
group by Date, DeviceOs
order by 1;
select Date, count(distinct Ip + Useragent) as cnt
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date >= '2018-08-01'
and Date <= '2018-10-31'
and DeviceOs = 'iOS'
group by Date
having count(*) > 100
order by 1, 2
select Date,
Hour,
Ip,
Useragent,
ClientType,
AcastId,
DeviceOs,
count(*) as cnt
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date >= '2018-09-30'
and Date <= '2018-10-02'
group by Date, Hour, Ip, Useragent, ClientType, AcastId, DeviceOs
order by Date, Hour, AcastId, cnt desc;
select Date, Hour, Ip, Useragent, ClientType, DeviceOs, count(*) as cnt
from DownloadStats
where AcastId = '70e7f53b-0495-4dac-b2a1-daa4514ea7cb'
and Date >= '2018-09-30'
and Date <= '2018-10-02'
group by Date, Hour, Ip, Useragent, ClientType, DeviceOs
order by Date, Hour, cnt desc;
select Date,
Hour,
Ip,
Useragent,
ClientType,
AcastName,
DeviceOs,
count(*) as cnt
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date >= '2018-09-30'
and Date <= '2018-10-02'
group by Date, Hour, Ip, Useragent, ClientType, AcastName, DeviceOs
order by cnt desc;
select Date, Hour, count(*)
from dbo.DownloadStats
where Date >= '2018-09-25'
and Date <= '2018-10-05'
and ClientType in ('rss', 'embed')
and ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
group by Date, Hour
order by 1, 2;
select Date, count(*) as cnt, Useragent, DeviceOs, DeviceType
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date >= '2018-09-29'
and Date <= '2018-10-02'
group by Date, Useragent, DeviceOs, DeviceType
having count(*) > 100
order by Date asc, cnt desc
select Date, count(*) as cnt, Useragent, DeviceOs, AcastName
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date in ('2018-09-27', '2018-10-01', '2018-10-22')
group by Date, Useragent, DeviceOs, AcastName
having count(*) > 500
order by Date asc, cnt desc
select Date, Useragent, max(t1.cnt)
from (select Date, Useragent, count(*) as cnt
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date >= '2018-09-01'
and Date <= '2018-10-31'
group by Date, Useragent
order by 1, 2) t1
group by t1.Date, t1.Useragent
order by 1, 2;
-- This is a wrong one but happen to discover clear Text IP
select Date, Ip, count(ip) as cnt_ip, count(distinct ip) as cnt_distinct_ip
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date = '2018-10-01'
and Useragent like 'AppleCoreMedia/1.0.0.15G77%'
or Useragent like 'AppleCoreMedia/1.0.0.16A366%'
group by Date, Ip
order by 1, cnt_ip desc;
-- This is a wrong one but happen to discover clear Text IP
select Date, Ip, count(ip) as cnt_ip, count(distinct ip) as cnt_distinct_ip
from DownloadStats
where ChannelId = '307373f9-d0e5-46b1-8050-b4b2937b8281'
and Date = '2018-10-01'
and (Useragent like 'AppleCoreMedia/1.0.0.15G77%' or Useragent like 'AppleCoreMedia/1.0.0.16A366%')
group by Date, Ip
order by 1, cnt_ip desc;
select Date, count(*) as cnt
from DownloadStats
where Ip like '[0-9]%'
and Ip like '%[0-9]'
group by Date, Ip
order by Date desc, cnt desc;
select Date, Ip, count(*) as cnt
from AdImpressionStats
where Ip like '[0-9]%'
and Ip like '%[0-9]'
group by Date, Ip
order by Date desc, cnt desc;
select count(distinct IP + Useragent), Date
from DownloadStats
where Date >= '2018-08-01'
and Date <= '2018-10-31'
and ChannelId in (select distinct id from Shows where NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8')
group by Date
order by Date;
select distinct id
from Shows
where NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8';
select top 100 *
from DownloadStats
where ChannelId = 'a14ac9f7-803d-4620-bf3f-c17bc8ddb2ec';
select date,
count(distinct (Ip + Useragent)) as count_distinct,
count(*) as count_total,
CONCAT(count(distinct (Ip + Useragent)) / cast(count(*) as float) * 100, '%') as distinct_percentage
from AdImpressionStats
where Date >= '2018-10-01'
group by Date
order by Date;
select concat(DATE, '-', Hour) as date_hour, count(*) as cnt
from AdImpressionStats
where Date >= '2018-11-15'
group by Date, Hour
order by Date, Hour;
select DATE, count(id) as cnt
from DownloadStats
where Date >= '2018-11-15'
group by Date
order by Date;
select concat(DATE, '-', Hour) as date_hour, count(*) as cnt
from DownloadStats
where Date >= '2018-11-15'
group by Date, Hour
order by Date, Hour;
select concat(DATE, '-', Hour) as date_hour, count(Date) as cnt
from AdImpressionStats
where Date >= '2018-11-15'
group by Date, Hour
order by Date, Hour;
select concat(DATE, '-', Hour) as date_hour, count(Date) as cnt
from DownloadStats
where Date >= '2018-11-15'
group by Date, Hour
order by Date, Hour;
select sum(case when ImpressionUrl like '%listenerId%' then 1 else 0 end) as count_has_listenerIDs,
count(*) as count_all_ads,
CONCAT(sum(case when ImpressionUrl like '%listenerId%' then 1 else 0 end) / cast(count(*) as float) * 100,
'%') as percentage
from AdImpressionStats
where Date >= '2018-11-01'
group by Date
order by Date;
select max(Timestamp)
from AdImpressionStats;
select Country, Date, count(*) as cnt
from AdImpressionStats
where Date = '2018-11-12'
group by Country, Date
order by 3 desc, 1, 2;
select count(distinct Ip + Useragent)
from AdImpressionStats
where Date = '2018-11-12';
select count(*)
from AdImpressionStats
where Date = '2018-11-12';
select Date, Hour, Timestamp, Week, Year, AcastId
from DownloadStats
where Date = '2018-11-15'
and (SourceBucket like '%2018/11/20/%');
select ImpressionUrl
from AdImpressionStats
where ImpressionUrl like
'%reqType=AdsSetup&protocolVersion=2.0-compliant&zoneId=[%7Bz:188,n:1%7D]&companionZones=190&cb=4a33b1fc-b825-4772-be21-e43c33342cba&aw_0_1st.contentcategory=comedy&maxAds=1&aw_0_1st.device=podcasts&aw_0_1st.devicetype=other&aw_0_1st.showcountry=GB&aw_0_1st.os=other&aw_0_1st.platform=rss&aw_0_1st.rating=no&aw_0_1st.salesvertical=[%22comedy%22,%22filmentertainment%22,%22curiousthinkers%22,%22lifestyle%22,%22women%22,%22parentingfamilies%22,%22women2544%22,%22women45%22]&aw_0_1st.show=standardissuespodcast&aw_0_1st.owneroffice=london&aw_0_azn.brandName=acast&aw_0_awz.companionRequest=true&aw_0_awz.g=%5B%22m%22%5D&aw_0_azn.gender=%5B%22m%22%5D&aw_0_azn.pname=%5B%22Acast%20General%20Inventory%22%5D&aw_0_req.gdprConsentGiven=false&aw_0_awz.accuracy=100&aw_0_azn.brandDomain=http%3A%2F%2Fwww.acast.com&aw_0_awz.a=%5B%225564%22%5D&aw_0_azn.zposition=%5B%22mid-roll%22%5D&aw_0_awz.poi=%5B%5D&aw_0_azn.age=%5B%225564%22%5D%'
and DATE = '2018-11-12';
select count(distinct Ip + Useragent)
from DownloadStats
where Date = '2018-11-12';
select Date, count(id) as cnt
from DownloadStats
group by Date
order by Date;
select top 100 *
from SessionStats
select top 100 Birthday
from UserData
select UserId, DATEDIFF(YEAR, Cast(Birthday as DATE), GETDATE()) as age, Gender
from UserData;
select ChannelUrl, t2.age, t2.Gender
from DownloadStats t1
left outer join (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData) t2
on t1.UserId = t2.UserId
where ClientType = 'app'
and ChannelUrl in
('sparpodcast')
-- ('sparpodcast', 'sakerstil', 'rebeccaochvanessa', 'alltduvelatveta', 'dellasport', 'vadblirdetformord', 'mordmotmord', 'pillowtalk', 'mittilivet', 'mathildaochandrea', 'lundh', 'markochjonas', 'sportbladetsplp', 'lillelordag', 'tvasystrarenpodd', 'studioallsvenskan', 'lakarpodden')
and date = '2018-11-11'
select (select DATEDIFF(YEAR, Birthday, GETDATE()) as age from UserData) t2
group by age
select age, count(*) as counting
from (select DATEDIFF(YEAR, Birthday, GETDATE()) as age from UserData) t
group by age
order by age
-- select top 100 ChannelUrl, t2.age, t2.Gender
-- from DownloadStats t1
-- left outer join (select UserId, DATEDIFF(YEAR, Cast(Birthday as DATE), GETDATE()) as age, Gender
-- from UserData where DATEDIFF(YEAR, Cast(Birthday as DATE), GETDATE()) < 100) t2 on t1.UserId = t2.UserId
-- where ClientType = 'app'
-- and ChannelUrl in ('sparpodcast')
-- -- ('sparpodcast', 'sakerstil', 'rebeccaochvanessa', 'alltduvelatveta', 'dellasport', 'vadblirdetformord', 'mordmotmord', 'pillowtalk', 'mittilivet', 'mathildaochandrea', 'lundh', 'markochjonas', 'sportbladetsplp', 'lillelordag', 'tvasystrarenpodd', 'studioallsvenskan', 'lakarpodden')
-- and date >= '2018-11-11'
select ChannelUrl,
t2.age,
t2.Gender,
sum(case when t2.age > 0 and t2.age < 18 then 1 else 0 end) as age_0_18_sum,
sum(case when t2.age >= 18 and t2.age < 24 then 1 else 0 end) as age_18_24_sum,
sum(case when t2.age >= 25 and t2.age < 34 then 1 else 0 end) as age_25_34_sum,
sum(case when t2.age >= 35 and t2.age < 44 then 1 else 0 end) as age_35_44_sum,
sum(case when t2.age >= 45 and t2.age < 85 then 1 else 0 end) as age_45_85_sum,
sum(case when t2.Gender = 'male' then 1 else 0 end) as male_sum,
sum(case when t2.Gender = 'female' then 1 else 0 end) as female_sum,
sum(case when t2.Gender = '' then 1 else 0 end) as empty_gender_sum
from DownloadStats t1
left outer join (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData) t2
on t1.UserId = t2.UserId
where ClientType = 'app'
and ChannelUrl in
('sparpodcast')
-- ('sparpodcast', 'sakerstil', 'rebeccaochvanessa', 'alltduvelatveta', 'dellasport', 'vadblirdetformord', 'mordmotmord', 'pillowtalk', 'mittilivet', 'mathildaochandrea', 'lundh', 'markochjonas', 'sportbladetsplp', 'lillelordag', 'tvasystrarenpodd', 'studioallsvenskan', 'lakarpodden')
and date = '2018-11-11'
group by ChannelUrl, t2.age, t2.Gender
order by age
select count(id) as total_count,
sum(case when t2.age > 0 and t2.age < 18 then 1 else 0 end) as age_0_18_sum,
sum(case when t2.age >= 18 and t2.age < 24 then 1 else 0 end) as age_18_24_sum,
sum(case when t2.age >= 25 and t2.age < 34 then 1 else 0 end) as age_25_34_sum,
sum(case when t2.age >= 35 and t2.age < 44 then 1 else 0 end) as age_35_44_sum,
sum(case when t2.age >= 45 and t2.age < 85 then 1 else 0 end) as age_45_85_sum,
sum(case when t2.age >= 85 then 1 else 0 end) as age_85_plus_sum,
sum(case when t2.Gender = 'male' then 1 else 0 end) as male_sum,
sum(case when t2.Gender = 'female' then 1 else 0 end) as female_sum,
sum(case when t2.Gender = '' then 1 else 0 end) as empty_gender_sum
from DownloadStats t1
left outer join (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData) t2
on t1.UserId = t2.UserId
where ClientType = 'app'
and ChannelUrl in
('aliceochbianca')
-- ('sparpodcast', 'sakerstil', 'rebeccaochvanessa', 'alltduvelatveta', 'dellasport', 'vadblirdetformord', 'mordmotmord', 'pillowtalk', 'mittilivet', 'mathildaochandrea', 'lundh', 'markochjonas', 'sportbladetsplp', 'lillelordag', 'tvasystrarenpodd', 'studioallsvenskan', 'lakarpodden')
and year = 2018
select top 100 Country
from DownloadStats
where ClientType = 'app'
and year = 2018;
select ChannelUrl, t2.age, t2.Gender
from DownloadStats t1
left outer join (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData) t2
on t1.UserId = t2.UserId
where ClientType = 'app'
and ChannelUrl in
('alice')
-- ('sparpodcast', 'sakerstil', 'rebeccaochvanessa', 'alltduvelatveta', 'dellasport', 'vadblirdetformord', 'mordmotmord', 'pillowtalk', 'mittilivet', 'mathildaochandrea', 'lundh', 'markochjonas', 'sportbladetsplp', 'lillelordag', 'tvasystrarenpodd', 'studioallsvenskan', 'lakarpodden')
and year = 2018
group by ChannelUrl,
t2.age,
t2.Gender
order by ChannelUrl, t2.age;
select max(ChannelUrl) as channel_url,
sum(case when t2.age > 0 and t2.age < 18 then 1 else 0 end) as age_0_18_sum,
sum(case when t2.age >= 18 and t2.age < 24 then 1 else 0 end) as age_18_24_sum,
sum(case when t2.age >= 25 and t2.age < 34 then 1 else 0 end) as age_25_34_sum,
sum(case when t2.age >= 35 and t2.age < 44 then 1 else 0 end) as age_35_44_sum,
sum(case when t2.age >= 45 and t2.age < 85 then 1 else 0 end) as age_45_85_sum,
sum(case when t2.age >= 85 then 1 else 0 end) as age_85_plus_sum,
sum(case when t2.Gender = 'male' then 1 else 0 end) as male_sum,
sum(case when t2.Gender = 'female' then 1 else 0 end) as female_sum,
sum(case
when t2.Gender <> 'male' and t2.Gender <> 'female' then 1
else 0 end) as other_gender_sum
from DownloadStats t1
left outer join (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData) t2
on t1.UserId = t2.UserId
where ClientType = 'app'
and ChannelUrl in
('aliceochbianca')
and Country = 'SE'
and Year = 2018;
----- ALL -----
WITH App as (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData)
select ChannelUrl, Year, App.age, App.Gender, count(ChannelUrl) as counting
from DownloadStats t1
left outer join App on t1.UserId = App.UserId
where ClientType = 'app'
group by Year,
App.age,
App.Gender,
ChannelUrl
order by ChannelUrl, Year, App.age, App.Gender;
select Referer, Useragent
from DownloadStats
where ChannelUrl = 'theeconomistasks'
and City = 'Mount Holly'
and Date = '2018-11-04'
select Useragent, count(Useragent) as cnt
from DownloadStats
where ChannelUrl = 'theeconomistasks'
and City = 'Mount Holly'
and Year = 2018
and Month = 10
group by Useragent
order by 2 desc
select Referer, count(Referer) as cnt
from DownloadStats
where ChannelUrl = 'theeconomistasks'
and City = 'Ashburn'
and Year = 2018
and Month = 10
group by Referer
order by 2 desc
select ISP, count(ISP)
from DownloadStats
where ChannelUrl = 'theeconomistasks'
and City = 'Ashburn'
group by ISP
order by 2 desc
select top 100 City, AcastUrl, ChannelUrl
from DownloadStats
where AcastUrl like '%theeconomistmoneytalks%'
or ChannelUrl like '%theeconomistmoneytalks%'
----- ALL + Country-----
WITH App as (select UserId, DATEDIFF(YEAR, Birthday, GETDATE()) as age, Gender from UserData)
select ChannelUrl, Year, App.age, App.Gender, Country, count(ChannelUrl) as counting
from DownloadStats t1
left outer join App on t1.UserId = App.UserId
where ClientType = 'app'
group by Year,
App.age,
App.Gender,
ChannelUrl,
Country
order by ChannelUrl, Year, App.age, App.Gender, Country;
select count(Gender) as counting, Gender
from UserData
group by Gender;
select Useragent, Country, count(Useragent) as cnt
from DownloadStats
where ChannelId = 'f2cb4af8-bcd9-4baa-9908-3a9dd3d973f4'
and Year = 2018
and Month = 10
group by Useragent, Country
order by cnt desc
select Date, Ip, Useragent, AcastUrl, referer, count(*) as cnt
from DownloadStats
where ChannelId = 'f2cb4af8-bcd9-4baa-9908-3a9dd3d973f4'
and Country = 'DE'
and Year = 2018
and Month = 11
and Ip = 'mNCrytfNoQipM4HwPzYgrw=='
group by IP, Useragent, Date, referer, AcastUrl
order by Date, AcastUrl, cnt desc
select top 100 *
from DownloadStats
where Date = '2018-12-01'
select substring(Useragent, 1, charindex('/', Useragent)) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3) as ua,
count(substring(Useragent, 1, charindex('/', Useragent))) as cnt
from DownloadStats
-- where Date = '2018-12-01'
where Year = 2018
and Month = 11
group by substring(Useragent, 1, charindex('/', Useragent)) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
order by 2 desc
select substring(Useragent, 1, charindex('/', Useragent)) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3) as ua
from DownloadStats
-- where Date = '2018-12-01'
where Year = 2018
and Month = 11
group by substring(Useragent, 1, charindex('/', Useragent)) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
order by 2 desc
with UA as (select DATE,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent not like '%/%' then '(others)'
when Useragent like '(null)/(null) watchOS%'
then '(null)/(null) watchOS'
when Useragent like 'AppleCoreMedia%' then
substring(Useragent, 1, charindex('/', Useragent) - 1) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
else substring(Useragent, 1, charindex('/', Useragent) - 1) end) as ua_parsed
from DownloadStats
where Date <= '2018-12-03')
select UA.Date, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ua_parsed
having count(UA.ua_parsed) > 300
order by UA.Date, count(UA.ua_parsed) desc;
-- v2 --
with UA as (select DATE,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent like '(null)/(null) watchOS/5%'
then '(null)/(null) watchOS 5'
when Useragent like 'AppleCoreMedia%iPhone%'
then 'iPhone OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPad%'
then 'iPad OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%HomePod%'
then 'HomePod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%Apple Watch%'
then 'Apple Watch OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPod%'
then 'iPod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like '%/%' then substring(Useragent, 1, charindex('/', Useragent) - 1)
else '(unknown)' end) as ua_parsed
from DownloadStats
where Date <= '2018-12-03')
select UA.Date, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ua_parsed
-- having count(UA.ua_parsed) > 300
order by UA.Date, count(UA.ua_parsed) desc;
select Date, Useragent, count(Useragent) as counting
from DownloadStats
where Year = 2018
and lower(Useragent) like '%guardian%'
group by Date, Useragent
order by Date, counting desc
select Useragent, count(Useragent) as cnt
from DownloadStats
where Year = 2018
and Month = 12
and (lower(Useragent) like 'podcast/%' or lower(Useragent) like 'podcasts/%')
group by Useragent
order by 2 desc;
select Year, Month, Useragent, count(Useragent) as cnt
from DownloadStats
where Year = 2018
and (lower(Useragent) like 'podcast/%' or lower(Useragent) like 'podcasts/%')
group by Useragent, Year, Month
order by Year desc, Month desc, count(Useragent) desc;
select Year, Month, Useragent, count(Useragent) as cnt
from DownloadStats
where Year = 2018
and (lower(Useragent) like 'appletv%')
group by Useragent, Year, Month
order by Year desc, Month desc, count(Useragent) desc;
with UA as (select DATE,
ChannelUrl,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent not like '%/%' then '(others)'
when Useragent like '(null)/(null) watchOS%'
then '(null)/(null) watchOS'
when Useragent like 'AppleCoreMedia%' then
substring(Useragent, 1, charindex('/', Useragent) - 1) +
substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
else substring(Useragent, 1, charindex('/', Useragent) - 1) end) as ua_parsed
from DownloadStats
where Year = 2018)
select UA.Date, UA.ChannelUrl, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ChannelUrl, UA.ua_parsed
having count(UA.ua_parsed) > 50
order by UA.Date, count(UA.ua_parsed) desc;
-- v3 --
with UA as (select DATE,
ChannelUrl,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent like '(null)/(null) watchOS/5%'
then '(null)/(null) watchOS 5'
when Useragent like 'AppleCoreMedia%iPhone%'
then 'iPhone OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPad%'
then 'iPad OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%HomePod%'
then 'HomePod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%Apple Watch%'
then 'Apple Watch OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPod%'
then 'iPod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like '%/%' then substring(Useragent, 1, charindex('/', Useragent) - 1)
else '(unknown)' end) as ua_parsed
from DownloadStats
where Date <= '2018-12-05')
select UA.Date, UA.ChannelUrl, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ChannelUrl, UA.ua_parsed
having count(UA.ua_parsed) > 30
order by UA.Date, count(UA.ua_parsed) desc;
-- v4 --
with UA as (select DATE,
ChannelUrl,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent like '(null)/(null) watchOS/5%'
then 'Apple (null)/(null) watchOS 5'
when Useragent like 'AppleCoreMedia%iPhone%'
then 'Apple iPhone OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPad%'
then 'Apple iPad OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%HomePod%'
then 'Apple HomePod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%Apple Watch%'
then 'Apple Watch OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPod%'
then 'Apple iPod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like '%/%' then substring(Useragent, 1, charindex('/', Useragent) - 1)
else '(others)' end) as ua_parsed
from DownloadStats
where Date >= '2017-01-01'
and Date <= '2018-12-13'
and ChannelUrl is not null
and ChannelUrl != '')
select UA.Date, UA.ChannelUrl, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ChannelUrl, UA.ua_parsed
order by UA.Date, count(UA.ua_parsed) desc;
select Date, Country, City, Isp, count(Country) as counting
from AdImpressionStats
where ChannelUrl = 'theeconomistasks'
and City = 'Ashburn'
group by Date, City, Country, Isp
order by Date asc;
SELECT year, month, day, clientType, count(clientType) as counting
FROM DownloadStats
where date >= '2018-12-05'
group by clientType, year, month, day
order by year, month, day, clientType
with UA as (select DATE,
(case
when Useragent like '(null)/(null) watchOS/5%'
then '(null)/(null) watchOS 5'
else 'others' end) as ua_parsed
from DownloadStats
where Date >= '2018-06-01')
select UA.Date, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ua_parsed
order by UA.Date, UA.ua_parsed;
select DeviceOs, DeviceOs, DeviceVersion, ClientType, Useragent, count(Useragent) as counting
from DownloadStats
where Date = '2018-12-11'
group by DeviceOs, DeviceOs, DeviceVersion, ClientType, Useragent
order by count(Useragent) desc
select top 100 *
from SessionStats
where EndSecond != 0;
select max(Modified)
from Episodes;
-- v5 --
with t as (select UA.Date,
UA.ChannelUrl,
case
when count(UA.ua_parsed) < 30 then '(others)'
else UA.ua_parsed end as ua_parsed,
count(UA.ua_parsed) as cnt
from (select DATE,
ChannelUrl,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent like '(null)/(null) watchOS/5%'
then 'Apple (null)/(null) watchOS/5'
when Useragent like 'AppleCoreMedia%iPhone%'
then 'Apple iPhone OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPad%'
then 'Apple iPad OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%HomePod%'
then 'Apple HomePod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%Apple Watch%'
then 'Apple Watch OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPod%'
then 'Apple iPod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like '%/%' then substring(Useragent, 1, charindex('/', Useragent) - 1)
else '(others)' end) as ua_parsed
from DownloadStats
where Date <= '2018-12-13'
and ChannelUrl is not null
and ChannelUrl != '') UA
group by UA.Date, UA.ChannelUrl, UA.ua_parsed)
select t.Date, t.ChannelUrl, t.ua_parsed, sum(t.cnt) as total
from t
group by t.Date, t.ChannelUrl, t.ua_parsed
order by t.Date, sum(t.cnt) desc
with UA as (select DATE,
ChannelUrl,
(case
when Useragent is null or Useragent = '' then '(empty)'
when Useragent like '(null)/(null) watchOS/5%'
then 'Apple (null)/(null) watchOS 5'
when Useragent like 'AppleCoreMedia%iPhone%'
then 'Apple iPhone OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPad%'
then 'Apple iPad OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%HomePod%'
then 'Apple HomePod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%Apple Watch%'
then 'Apple Watch OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like 'AppleCoreMedia%iPod%'
then 'Apple iPod OS' + substring(Useragent, charindex('CPU OS', Useragent) + 6, 3)
when Useragent like '%/%' then substring(Useragent, 1, charindex('/', Useragent) - 1)
else '(others)' end) as ua_parsed
from AdImpressionStats
where Date <= '2018-12-13')
select UA.Date, UA.ChannelUrl, UA.ua_parsed, count(UA.ua_parsed) as cnt
from UA
group by UA.Date, UA.ChannelUrl, UA.ua_parsed
having count(UA.ua_parsed) > 20
order by UA.Date, count(UA.ua_parsed) desc;
select Date, Hour, count(Hour) as cnt
from DownloadStats
where Date >= '2019-01-23'
group by Date, Hour
order by Date, Hour;
select Date, Hour, count(Hour) as cnt
from AdImpressionStats
where Date >= '2018-12-01'
group by Date, Hour
order by Date, Hour;
select cast(Timestamp as DATE), DATEPART(HOUR, Timestamp), count(*) as cnt
from SessionStats
where cast(Timestamp as DATE) >= '2018-12-01'
group by cast(Timestamp as DATE), DATEPART(HOUR, Timestamp)
order by cast(Timestamp as DATE), DATEPART(HOUR, Timestamp);
SELECT download_stats.ClientType AS "download_stats.client_type",
COUNT(*) AS "download_stats.count"
FROM dbo.DownloadStats AS download_stats
GROUP BY download_stats.ClientType
select ClientType, COUNT_BIG(*)
from DownloadStats
group by ClientType
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed', 'Failed', 'Cancelled')
AND session_id <> session_id()
ORDER BY submit_time DESC;
select top 500 *
from Payouts
select Date, ChannelUrl, ClientType, count(*) as counting
from DownloadStats
where Date like '2018-1%'
group by Date, ChannelUrl, ClientType
having count(*) > 20;
select ChannelUrl, ChannelId, Hosted
from DownloadStats
where ChannelName is not null
and ChannelName != ''
and Hosted = 'True'
group by ChannelUrl, ChannelId, Hosted
order by ChannelUrl
select top 50 ChannelName
from DownloadStats
where ChannelName like '%' + CHAR(9) + '%'
SELECT t1.Year,
t1.Country,
COUNT_BIG(*) as counting
FROM DownloadStats t1
LEFT JOIN dbo.Shows AS shows ON t1.ChannelId = shows.Id
WHERE t1.Hosted = 1
and shows.NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8'
GROUP BY t1.Year, t1.Country
ORDER BY Year, COUNT_BIG(*) DESC;
SELECT Year,
Country,
COUNT_BIG(*) as counting
FROM DownloadStats
where ChannelId in (select distinct Id from dbo.Shows where NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8')
and Hosted = 1
group by Year, Country
order by Year, COUNT_BIG(*) desc;
SELECT t1.Year,
t1.Country,
COUNT_BIG(*) as counting
FROM DownloadStats t1
JOIN dbo.Shows AS shows ON t1.ChannelId = shows.Id
AND (shows.NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8')
WHERE t1.Hosted = 1
GROUP BY t1.Year, t1.Country
ORDER BY t1.Year, COUNT_BIG(*) DESC;
SELECT top 1000 *
FROM DownloadStats t1
JOIN dbo.Shows AS shows ON t1.ChannelId = shows.Id
AND shows.NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8'
WHERE t1.Year = 2018
and t1.Hosted = 1
and Country = 'GB';
select Country, count(*)
from DownloadStats
group by Country
order by 2 desc;
select top 100 *
from AdImpressionStats;
select date, Hour, ClientType, count(*) as cnt
from DownloadStats
where date = '2019-02-11'
group by date, Hour, ClientType
ORDER BY 1, 2, 4 desc, 3
SELECT Year,
Month,
Country,
Count(distinct (IP + Useragent)) as unique_count,
COUNT(*) as total_count
FROM DownloadStats
where ChannelId in (select distinct Id from dbo.Shows where NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8')
and Hosted = 1
and Year = 2018
group by Year, Month, Country
order by Year, Month, COUNT(*) desc;
-- BBC
SELECT Date,
Country,
Count(distinct (IP + Useragent)) as unique_count,
COUNT(*) as total_count
FROM DownloadStats
where ChannelId in (select distinct Id from dbo.Shows where NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8')
and Hosted = 1
and Year = 2018
group by Date, Country
order by Date;
select Date, Country, Useragent, COUNT(*)
from AdImpressionStats
where Date >= '2019-01-25'
group by Date, Country, Useragent
Having COUNT(*) > 100
order by 1, 2, 4 desc
select Date, Country, Useragent, COUNT(*)
from DownloadStats
where Date >= '2019-01-25'
group by Date, Country, Useragent
Having COUNT(*) > 100
order by 1, 2, 4 desc
select date, Country, count(distinct (IP + Useragent)) as distinct_IP_UA_counting
from AdImpressionStats
where Date >= '2019-02-01'
and Date <= '2019-02-14'
group by Date, Country
order by Date, Country
select count(Date)
from DownloadStats
where year = 2018
select Date, Hour, count(Date) as counting
from dbo.DownloadStats
where Date >= '2019-02-27'
group by Date, Hour
order by Date, Hour;
select sum(Bytes) as sum_bytes
from dbo.ShowsDataTransfer
where ShowId in (select ShowId from dbo.Shows where NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8')
and cast(Timestamp as Date) = '2019-02-26'
select cast(Timestamp as Date), SUM(Bytes) as sum_bytes
from dbo.ShowsDataTransfer
where ShowId in (select distinct ShowId from dbo.Shows where NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8')
and cast(Timestamp as Date) >= '2019-02-20'
group by cast(Timestamp as Date)
order by cast(Timestamp as Date)
select YEAR(Timestamp) as year, MONTH(Timestamp) as month, SUM(Bytes) as sum_bytes
from dbo.ShowsDataTransfer
where ShowId in (select distinct ShowId from dbo.Shows where NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8')
and YEAR(Timestamp) >= 2018
group by YEAR(Timestamp), MONTH(Timestamp)
order by YEAR(Timestamp), MONTH(Timestamp);
select Year, Month, ChannelId, ChannelUrl, count(*) as dw_counting
from DownloadStats
where Year = 2019
and Month in (1, 2)
group by Year, Month, ChannelId, ChannelUrl
order by Year, Month, count(*) desc, ChannelId, ChannelUrl;
select adid, count(*)
from AdImpressionStats
where adid in (select distinct adid from adimpressionstats where cid = 'dragon-admanager')
and cid = ''
group by adid
order by 2 desc
SELECT count(*) as count_bbc_2019_03_01
FROM DownloadStats t1
JOIN dbo.Shows AS shows ON t1.ChannelId = shows.Id
AND shows.NetworkId = '7ac9db08-d041-4441-ac9a-fcbe8f270bb8'
WHERE t1.Date = '2019-03-01'
and t1.Hosted = 1;
SELECT count(*) as count_guardian_2019_03_01
FROM DownloadStats t1
JOIN dbo.Shows AS shows ON t1.ChannelId = shows.Id
AND shows.NetworkId = '9a9fa40b-b0fc-43a4-9a0a-91cc23dec1a8'
WHERE t1.Date = '2019-03-01'
and t1.Hosted = 1;
SELECT shows.NetworkId, count(*) as count_2019_03_01
FROM DownloadStats t1
JOIN dbo.Shows AS shows ON t1.ChannelId = shows.Id
WHERE t1.Date = '2019-03-01'
and t1.Hosted = 1
group by shows.NetworkId
order by 2 desc
-- old_dw
select Date, ChannelId as show_id, ChannelUrl as show_url, count(*) as old_dw_counting
from dbo.DownloadStats
where Date >= '2019-02-04'
and Date <= '2019-04-12'
and Hosted = 1
group by Date, ChannelId, ChannelUrl
order by 1, 4 desc, 2
-- old_dw: ad
select Date as dt, AdId as ad_id, ChannelId as show_id, count(*) as ad_old_dw_counting
from dbo.AdImpressionStats
where Date >= '2019-02-04'
and Date <= '2019-04-12'
and Hosted = 1
group by Date, AdId, ChannelId
order by 1, 4 desc, 2, 3
SELECT Date,
sum(case when lower(Useragent) like '%null%watchos/5%' then 1 else 0 end) as watch_os_count,
count(*) as count_total,
cast(sum(case when lower(Useragent) like '%null%watchos/5%' then 1 else 0 end) as float) / count(*) as percentage
FROM DownloadStats
WHERE Date >= '2019-01-01'
group by Date
order by Date
select max(Modified)
from UserData
-- 2019-04-01 00:00:00.9220000
select max(TIMESTAMP)
from ShowsDataTransfer
-- 2019-03-31 23:59:59.000
select max(Modified)
from Shows
-- 2019-03-31 23:59:48.6010000
select max(Modified)
from SalesVerticals
-- 2018-03-12 13:04:03.4230000
select max(Modified)
from Networks
-- 2018-07-03 13:16:16.8630000
select max(Modified)
from Follows
-- 2019-03-31 23:59:14.1260000
select max(Modified)
from Flights
-- 2019-03-31 23:01:25.8120000
select max(Modified)
from Estimates
-- 2019-03-22 14:33:10.3150000
select max(Modified)
from Episodes
-- 2018-07-04 10:22:08.6780000
select max(Modified)
from Creatives
-- 2019-03-29 23:11:18.4060000
select max(Modified)
from Campaigns
-- 2019-03-31 22:52:34.6340000
select max(LastModifiedDate)
from CampaignItems
-- 2017-04-19 14:06:45.000
select max(Modified)
from Advertisers
-- 2019-03-29 15:17:20.1560000
-- Not Sure => Shows_SalesVerticals, ChannelCategories, Categories => since they do not have timestamp/modified date to check
select top 100 *
from AdImpressionStats
-- old_dw: ads
select Month as month, AdId as ad_id, ChannelId as show_id, count(*) as ad_old_dw_counting
from dbo.AdImpressionStats
where Date >= '2019-02-04' and Date <= '2019-04-12'
and Hosted = 1
group by month, AdId, ChannelId
order by 1, 4 desc, 2, 3
-- old_dw
select Month(Date) as month, ChannelId as show_id, ChannelUrl as show_url, Country as geo_country_iso, count(*) as old_dw_count
from dbo.DownloadStats
where Date >= '2019-02-04'
and Date <= '2019-04-12'
and Hosted = 1
group by Month(Date), ChannelId, ChannelUrl, Country
order by 1, 5 desc, 2, 3, 4
select top 100 * from systemtest.Flights
select top 100 * from dbo.Flights where Id='f56a4bd3-6715-45c7-8ede-25c63db6d147'
select * from sys.tables
select count(*) from dbo.Advertisers
select top 100 * from dbo.DownloadStats where Referer like '%google.com%'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment