-- Define the dialect
-- sqlfluff:dialect:sqlite

-- Set a smaller indent for this file
-- sqlfluff:indentation:tab_space_size:2

-- Set keywords to be capitalised
-- sqlfluff:rules:capitalisation.keywords:capitalisation_policy:upper

CREATE TABLE IF NOT EXISTS project_stats_t (
  value INTEGER,
  nb_projects INTEGER,
  avg_versions REAL,
  min_versions INTEGER,
  max_versions INTEGER,
  avg_wheels REAL,
  min_wheels INTEGER,
  max_wheels INTEGER,
  avg_source REAL,
  min_sources INTEGER,
  max_sources INTEGER
);

INSERT INTO project_stats_t
SELECT
  value,
  count(project_name),
  avg(nb_versions),
  min(nb_versions),
  max(nb_versions),
  avg(nb_wheels),
  min(nb_wheels),
  max(nb_wheels),
  avg(nb_sources),
  min(nb_sources),
  max(nb_sources)
FROM version_releases_stats, generate_series(0, 10, 1)
WHERE nb_versions >= value AND nb_versions < (value + 1)
GROUP BY value;

INSERT INTO project_stats_t
SELECT
  value,
  count(project_name),
  avg(nb_versions),
  min(nb_versions),
  max(nb_versions),
  avg(nb_wheels),
  min(nb_wheels),
  max(nb_wheels),
  avg(nb_sources),
  min(nb_sources),
  max(nb_sources)
FROM version_releases_stats, generate_series(10, 100, 5)
WHERE nb_versions >= value AND nb_versions < (value + 5)
GROUP BY value;

INSERT INTO project_stats_t
SELECT
  value,
  count(project_name),
  avg(nb_versions),
  min(nb_versions),
  max(nb_versions),
  avg(nb_wheels),
  min(nb_wheels),
  max(nb_wheels),
  avg(nb_sources),
  min(nb_sources),
  max(nb_sources)
FROM version_releases_stats, generate_series(100, 4700, 200)
WHERE nb_versions >= value AND nb_versions < (value + 200)
GROUP BY value;