0

Please login or register to vote for this query.

(click on this box to dismiss)

Grouping of the users in a 5-year large age groupings, with "under 20" and "60 and over " as the cut off points. All users with no age specific are entirely ignored. Also counts "active" users (those with an interaction in the last 3 months) Age groupings are arbitrary.

Q&A for pro webmasters

-- Users by (arbitrarily selected) Age Groups
-- Grouping of the users in a 5-year large age groupings,
-- with "under 20" and "60 and over " as the cut off points.
-- All users with no age specific are entirely ignored.
-- Also counts "active" users (those with an interaction in the last 3 months)
-- Age groupings are arbitrary.

WITH TotalUsers AS (
    SELECT CAST(COUNT(*) AS FLOAT) AS Count
    FROM Users
    WHERE Age IS NOT NULL
),ActiveUsers AS (
    SELECT CAST(COUNT(*) AS FLOAT) AS Count
    FROM Users
    WHERE Age IS NOT NULL
      AND LastAccessDate > dateadd(month,-3,getdate()) 
), AgeGroups AS (
SELECT
    Id,
    CASE
     WHEN Age < 20 THEN '-19'
     WHEN Age < 25 THEN '20-24'
     WHEN Age < 30 THEN '25-29'
     WHEN Age < 35 THEN '30-34'
     WHEN Age < 40 THEN '35-39'
     WHEN Age < 45 THEN '40-44'
     WHEN Age < 50 THEN '45-49'
     WHEN Age < 55 THEN '50-54'
     WHEN Age < 60 THEN '55-59'
    ELSE '60-' 
    END AS AgeGroup,
    CASE
      WHEN LastAccessDate > dateadd(month,-3,getdate()) THEN 1
      ELSE NULL
    END AS ActiveUser
FROM Users
WHERE Age IS NOT NULL
)

SELECT
   AgeGroup,
   COUNT(*) AS NumberOfUsers,
   ROUND( Count(*)/(SELECT Count FROM TotalUsers ) *100,3) AS Proportion,
   COUNT(ActiveUser) AS NumberOfActiveUsers,
   ROUND( Count(ActiveUser)/CAST(Count(*)AS FLOAT) *100,3 ) As PercActive,
   ROUND( Count(ActiveUser)/(SELECT Count FROM ActiveUsers ) *100,3) AS ActiveProportion

FROM AgeGroups 

GROUP BY AgeGroup
ORDER BY AgeGroup ASC

fork query

created jul 5 11 DMA57361