I have this table in SQL about the years in which students were studying in a school:

CREATE TABLE myt (
  student_name VARCHAR(50),
  student_year INT
);

INSERT INTO myt (student_name, student_year) VALUES
('john', 2010),
('John', 2011),
('John', 2012),
('John', 2019),
('John', 2020),
('alex', 2005),
('tim', 2000),
('tim', 2000),
('jack', 2020),
('jack', 2024);


 student_name student_year
         john         2010
         John         2011
         John         2012
         John         2019
         John         2020
         alex         2005
          tim         2000
          tim         2000
         jack         2020
         jack         2024

For each student, for all years between their min year and max year – I want to find out how many years they have missed and the percent of years they have missed.

The final result should look like this:

 student_name student_year total_years missed_years percent_missed
         john         2010           1            0              0
         john         2011           2            0              0
         john         2012           3            0              0
         john         2013           4            1             25
         john         2014           5            2             40
         john         2015           6            3             50
         john         2016           7            4           57.1
         john         2017           8            5           62.5
         john         2018           9            6           66.7
         john         2019          10            6             60
         john         2020          11            6           54.5
         alex         2005           1            0              0
          tim         2000           1            0              0
          tim         2000           1            0              0
         jack         2020           1            0              0
         jack         2021           2            1             50
         jack         2022           3            2           66.7
         jack         2023           4            3             75
         jack         2024           5            3             60

I tried to do the following approach:

• Since Netezza has very limited SQL functions (e.g. does not support recursive or correlated queries, or sequence generating functions – I decided to manually make a calendar CTE that contains all years between the earliest and latest years)
• Then, I tried to join this calendar CTE back to the original table
• From here, I tried to use a CASE WHEN statements to look at which years are missing and perform the calculations accordingly

Here is my approach:

WITH calendar_years AS (
    SELECT 2000 AS year UNION ALL
    SELECT 2001 UNION ALL
    SELECT 2002 UNION ALL
    SELECT 2003 UNION ALL
    SELECT 2004 UNION ALL
    SELECT 2005 UNION ALL
    SELECT 2006 UNION ALL
    SELECT 2007 UNION ALL
    SELECT 2008 UNION ALL
    SELECT 2009 UNION ALL
    SELECT 2010 UNION ALL
    SELECT 2011 UNION ALL
    SELECT 2012 UNION ALL
    SELECT 2013 UNION ALL
    SELECT 2014 UNION ALL
    SELECT 2015 UNION ALL
    SELECT 2016 UNION ALL
    SELECT 2017 UNION ALL
    SELECT 2018 UNION ALL
    SELECT 2019 UNION ALL
    SELECT 2020 UNION ALL
    SELECT 2021 UNION ALL
    SELECT 2022 UNION ALL
    SELECT 2023 UNION ALL
    SELECT 2024
),
student_years AS (
  SELECT 
    student_name,
    MIN(student_year) AS min_year,
    MAX(student_year) AS max_year
  FROM myt
  GROUP BY student_name
),
student_calendar AS (
  SELECT 
    s.student_name,
    c.year
  FROM student_years s
  JOIN calendar_years c ON c.year BETWEEN s.min_year AND s.max_year
),
filled_years AS (
  SELECT 
    sc.student_name,
    sc.year,
    CASE WHEN m.student_year IS NULL THEN 1 ELSE 0 END AS is_missing
  FROM student_calendar sc
  LEFT JOIN myt m ON sc.student_name = m.student_name AND sc.year = m.student_year
),
aggregated AS (
  SELECT 
    student_name,
    year,
    SUM(is_missing) OVER (PARTITION BY student_name ORDER BY year) AS missed_years,
    COUNT(*) OVER (PARTITION BY student_name ORDER BY year) AS total_years
  FROM filled_years
)
SELECT 
  student_name,
  year,
  total_years,
  missed_years,
  (missed_years * 1.0 / total_years * 1.0) * 100 AS percent_missed
FROM aggregated
ORDER BY student_name, year;

The final result looks like this:

 student_name year total_years missed_years percent_missed
         John 2011           1            0        0.00000
         John 2012           2            0        0.00000
         John 2013           3            1       33.33333
         John 2014           4            2       50.00000
         John 2015           5            3       60.00000
         John 2016           6            4       66.66667
         John 2017           7            5       71.42857
         John 2018           8            6       75.00000
         John 2019           9            6       66.66667
         John 2020          10            6       60.00000
         alex 2005           1            0        0.00000
         jack 2020           1            0        0.00000
         jack 2021           2            1       50.00000
         jack 2022           3            2       66.66667
         jack 2023           4            3       75.00000
         jack 2024           5            3       60.00000
         john 2010           1            0        0.00000
          tim 2000           2            0        0.00000
          tim 2000           2            0        0.00000

Does this approach make sense for solving this problem?