Calculate position change using the previous rows row_number value

Question

I'm trying to calculate the positional change based on the previous rows ROW_NUMBER() compared to the current ROW_NUMBER()

My query is using a recursive cte with a ROW_NUMBER() OVER .. clause which neatly gives me the row number of the result by its year.

WITH positions AS (
    SELECT 
        [incidents].state_id, [incidents].year_id, MAX(number_of_incidents) AS total_incidents_in_year,
        ROW_NUMBER() OVER(PARTITION BY [incidents].year_id ORDER BY MAX(number_of_incidents) DESC) AS position
    FROM
        [incidents]
    INNER JOIN
        years AS dy ON dy.year_id = [incidents].year_id
    INNER JOIN
        states AS ds on ds.state_id = [incidents].state_id
    GROUP BY
        [incidents].state_id, [incidents].year_id
)

After this my query then compares the positions to calculate the change between the row numbers.

SELECT
    ds.state_name, ds.state_id, [before].total_incidents_in_year, dy.year,
    [before].position AS before_position, [after].position AS after_position,
    ([before].position - [after].position) AS change
FROM
    positions AS [before]
LEFT JOIN 
    positions AS [after] ON [before].position = [after].position + 1 AND [before].state_id = [after].state_id AND [before].year_id = [after].year_id
INNER JOIN
    years AS dy ON dy.year_id = [before].year_id
INNER JOIN
    states AS ds on ds.state_id = [before].state_id
ORDER BY
    [before].year_id ASC, [before].total_incidents_in_year DESC

Unfortunately this does not work because the [after] position is always null.

This is a bit hard to explain so I've included a sqlfiddle link : http://www.sqlfiddle.com/#!18/c7e57e/1

--

This image visually explains what I'm trying to achieve

In 2011 Minnesota is in position 1, in 2012 Minnesota is in position 3, the change is +2

In 2011 Iowa is in position 6, in 2012 Iowa is in position 4, the change is -2

In 2011 South Dakota is in position 5, in 2012 South Dakota is in position 5, the change is 0

Thank you

score 1 · Answer 1 · answered Apr 25 '19 at 09:29

Figured it out.

I was incorrectly trying to join on the ROW_NUMBER() which would result in a mismatch join because the row numbers would not necessarily line up correctly with the state id.

After changing it to join on the year which is the correct way of calculate a year on year change it all came together.

WITH positions AS (
    SELECT 
        [incidents].state_id, dy.year, MAX(number_of_incidents) AS total_incidents_in_year,
        ROW_NUMBER() OVER(PARTITION BY dy.year ORDER BY MAX(number_of_incidents) DESC) AS position
    FROM
        [incidents]
    INNER JOIN
        years AS dy ON dy.year_id = [incidents].year_id
    INNER JOIN
        states AS ds on ds.state_id = [incidents].state_id
    GROUP BY
        [incidents].state_id, dy.year
)
SELECT
    ds.state_name, ds.state_id, [before].total_incidents_in_year, dy.year,
    [before].position AS before_position,
    ([before].position - [after].position) AS change
FROM
    positions AS [before]
LEFT JOIN 
    positions AS [after] ON [before].state_id = [after].state_id AND [before].year = [after].year + 1
INNER JOIN
    years AS dy ON dy.year = [before].year
INNER JOIN
    states AS ds on ds.state_id = [before].state_id
ORDER BY
    [before].year ASC, [before].total_incidents_in_year DESC

http://www.sqlfiddle.com/#!18/c7e57e/11

Calculate position change using the previous rows row_number value

1 Answers1