0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who build and repair bicycles, people who train cycling, or commute on bicycles

WITH RankedQuestions AS (
  SELECT 
    Id,
    CAST(ViewCount AS BIGINT) AS ViewCount,
    ROW_NUMBER() OVER (ORDER BY ViewCount DESC) AS row_num,
    COUNT(*) OVER () AS total_questions
  FROM Posts
  WHERE PostTypeId = 1  -- 1 represents questions in the Posts table
),
TopPercentViews AS (
  SELECT 
    SUM(CASE WHEN row_num <= CEILING(0.01 * total_questions) THEN CAST(ViewCount AS FLOAT) ELSE 0 END) AS top_1_views,
    SUM(CASE WHEN row_num <= CEILING(0.05 * total_questions) THEN CAST(ViewCount AS FLOAT) ELSE 0 END) AS top_5_views,
    SUM(CASE WHEN row_num <= CEILING(0.10 * total_questions) THEN CAST(ViewCount AS FLOAT) ELSE 0 END) AS top_10_views
  FROM RankedQuestions
),
TotalViews AS (
  SELECT SUM(CAST(ViewCount AS FLOAT)) AS total_views
  FROM Posts
  WHERE PostTypeId = 1  -- 1 represents questions in the Posts table
)
SELECT 
  (top_1_views * 100.0 / NULLIF(total_views, 0)) AS top_1_percent_view_ratio,
  (top_5_views * 100.0 / NULLIF(total_views, 0)) AS top_5_percent_view_ratio,
  (top_10_views * 100.0 / NULLIF(total_views, 0)) AS top_10_percent_view_ratio
FROM TopPercentViews, TotalViews;

fork query

edited sep 7

created sep 7 anonymous