godfucked.sql

select 
    count(0) AS `downloads`,
    `d`.`timestamp` AS `timestamp`,
    'Pack' AS `dltype`,
    `p`.`size` AS `size`,
    concat(`t`.`theme`,' #',`p`.`packnum`) AS `name`,
    `p`.`beatmaps` AS `files`,
    `p`.`downloads` AS `totaldownloads`,
    concat_ws(';',`t`.`id`,`t`.`theme`,`p`.`packnum`) AS `linkdata` 
from 
    `beatmap_downloads` `d` 
     join `beatmap_packs` `p` 
     on  `p`.`packnum` = `d`.`packid` 
     and `p`.`themeid` = `d`.`themeid` 
    join `beatmap_themes` `t` timestamp`
where 
    ((`d`.`type` = 1) 
    and (`d`.`timestamp` between (unix_timestamp() - (((7 * 24) * 60) * 60)) and (unix_timestamp() + 1))) 
    group by `d`.`type`,`d`.`packid`,`d`.`themeid` 
    
union 

select 
    count(0) AS `downloads`,
    `d`.`timestamp` AS `timestamp`,
    'Beatmap' AS `dltype`,
    `m`.`filesize` AS `size`,
    concat('#',`m`.`mapid`,' - ',`m`.`map`) AS `name`,
    1 AS `files`,
    `m`.`downloads` AS `totaldownloads`,
    `m`.`mapid` AS `linkdata` 
from 
    `beatmap_downloads` `d` 
    join `beatmap_maps` `m` 
        on `m`.`mapid` = `d`.`packid` 
        and `m`.`id` = `d`.themeidˋ
where 
    `d`.`type` = 2 
    and `d`.`timestamp` between unix_timestamp() - (7 * 24 * 60 * 60) and unix_timestamp() + 1 

group by 
    `d`.`type`,
    `d`.`packid`,
    `d`.`themeid` 
order by `downloads` desc 
limit 0,100