sql - Getting rows with the highest SELECT COUNT from groups within a resultset -

i have sqlite database contains parsed apache log lines.

a simplified version of db's table (accesses) looks this:

|referrer|datestamp| +--------+---------+ |xy.de   | 20170414| |ab.at   | 20170414| |xy.de   | 20170414| |xy.de   | 20170414| |12.com  | 20170413| |12.com  | 20170413| |xy.de   | 20170413| |12.com  | 20170413| |12.com  | 20170412| |xy.de   | 20170412| |12.com  | 20170412| |12.com  | 20170412| |ab.at   | 20170412| |ab.at   | 20170412| |12.com  | 20170412| +--------+---------+ 

i trying retrieve top referrer each day performing sub query select count on referrer. afterwards select entries subquery have highest count:

select datestamp, referrer, count(*) accesses datestamp between '20170414' , '20170414' group referrer having count(*) = (select max(anz)                     (select count(*) anz                           accesses                          datestamp between '20170414' , '20170414'                          group referrer                         )                   ); 

the above approach works long perform query single date, falls apart query date ranges. how can achieve grouping date? interested in referrer highest count.

if want days combined single best referrer, then:

select referrer, count(*) anz  accesses datestamp between '20170414' , '20170414' group referrer order count(*) desc limit 1; 

i think might want information broken out day. if so, correlated subquery helps -- , cte well:

with dr (       select a.datestamp, a.referrer, count(*) cnt       accesses       datestamp between '20170414' , '20170414'       group a.referrer, a.datestamp      ) select dr.* dr dr.cnt = (select max(dr2.cnt)                 dr dr2                 dr2.datestamp = dr.datestamp                );