Show package ranking

[gingerwizard]

e.g. top 1% of packages downloaded.

Simple query. Add to downloads bar.

[gingerwizard]

WITH results AS ( SELECT sumIf(count, date > {min_date:String}::Date32 AND date > {max_date:String}::Date32 - toIntervalDay(1) AND date <= {max_date:String}::Date32) AS last_day,
    sumIf(count, date > {min_date:String}::Date32 AND date > {max_date:String}::Date32 - toIntervalWeek(1) AND date <= {max_date:String}::Date32) AS last_week,
    sumIf(count, date > {min_date:String}::Date32 AND date > {max_date:String}::Date32 - toIntervalMonth(1) AND date <= {max_date:String}::Date32) AS last_month,
    sumIf(count, date > {min_date:String}::Date32 AND date > {min_date:String}::Date32 AND date <= {max_date:String}::Date32) AS total
    FROM pypi.pypi_downloads_per_day WHERE (project = {package_name:String}) AND 1=1 AND 1=1 
    AND 1=1 ),
(
    SELECT uniq(project) FROM (
      SELECT project FROM pypi.pypi_downloads_per_day WHERE date > {min_date:String}::Date32 AND date <= {max_date:String}::Date32 GROUP BY project HAVING sum(count) > (SELECT total FROM results)   
    )
) as rank
SELECT rank, * FROM results

solves but alittle slow

[gingerwizard]

better

WITH
(   SELECT
    sum(count) AS total
    FROM pypi.pypi_downloads_per_day WHERE project = {package_name:String} AND 1=1 AND 1=1 AND date > {min_date:String}::Date32 AND date <= {max_date:String}::Date32 AND 1=1
) AS downloads,
(SELECT count() FROM ( SELECT project FROM pypi.pypi_downloads_per_day WHERE date > {min_date:String}::Date32 AND date <= {max_date:String}::Date32 GROUP BY project HAVING sum(count) > downloads )) as rank,
(SELECT uniqExact(project) FROM pypi.pypi_downloads_per_day WHERE date > {min_date:String}::Date32 AND date <= {max_date:String}::Date32 ) as total_packages
SELECT rank, total_packages