SQL: Determining Favorite Color on A Specific Date?

I am working with Netezza SQL.

Suppose there is a universe where people have a favorite color. We assume that they have this color forever - until they decide to change their color. If this happens, this new color is now their favorite color until further notice.

Each person can change their color multiple times in the same year. Each person can re-confirm their existing favorite color in the same year multiple times as well.

I have represented this information in the following table ("my_table"):

    id color       date
1  111   red 2005-01-01
2  111  blue 2005-05-05
3  111   red 2007-01-01
4  222   red 2003-01-01
5  222  blue 2003-01-03
6  333   red 2004-01-01
7  333   red 2004-05-05
8  333  blue 2009-01-01
9  444   red 1999-01-01
10 444  blue 2010-01-01
11 555  blue 2020-01-01
12 666  blue 2000-01-01
13 666  red  2004-01-01
14 777  blue 2004-01-01
15 777  red 2006-01-01

Using this table, we can infer information such as:

• In 2005, id=111 changed their color once and then again in 2007 - and from that point on, their favorite color is red until further notice
• In 2003, id=222 changed their color - and their favorite color is blue until further notice
• In 2005, the favorite color for id = 444 would be red
• id = 555 did not appear in the table until 2020

My Question:

• Suppose we take the most recent color for each ID as of 2005-01-01
• Next, we only select ID's that had a favorite color of Red as of 2005-01-01
• How many times did these ID's change their color between 2005-01-01 and 2015-01-01?

The final answer will look something like this:

  id color_changes
1 111             2
2 333             1
3 444             1
4 666             0

What I tried so far:

I tried to start this problem by writing the following query to find out the favorite color of each person as of 2015-01-01:

WITH CTE AS (
    SELECT id, color, date,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
    FROM my_table
    WHERE date <= '2015-01-01'
)
SELECT id, color
FROM CTE
WHERE rn = 1;

But based on the results - I don't think this is correct (e.g. id=333 has a favorite color of red in 2005-01-01 ... but in the SQL query, it is coming as blue):

   id color
1 111   red
2 222  blue
3 333  blue
4 444  blue
5 555  blue
6 666   red
7 777   red

I am now stuck and can't achieve the desired result. Can someone please show me how to correct this?

Thanks!

In your query you are looking for the color per Id as at 2005-01-01 but you're using 2015-01-01 (You seem to indicate both dates but your results are for 2005)

This is possibly a little tricker than it first appears but I think the following will give you your desired result.

Initially I number the rows starting at 1 being the color closest to the target date, and choose the color that's prevalent at the target date, and also a flag to indicate if subsequent entries have a changed color.

Then in a second CTE a cumulative sum based on the color being the desired "red" at the target date, this gives a flag against all the "valid" rows.

Finally selecting only the valid row, sum the flags indicating the color has changed.

with flags as (
  select *,
  Row_Number() over(partition by case when date <= '20050101' then id end order by date desc) rn,
  first_value(color) over(partition by case when date <= '20050101' then id end order by date desc) ColAtDate,
  case when Lag(color) over(partition by id order by date) <> color then 1 end changed
	from t
), v as (
	select *, 
	  Sum(case when colatdate = 'red' and rn = 1 then 1 end) over(partition by id order by rn) valid
	from flags
    where date <= '20150101'
)
select id, Coalesce(Sum(case when rn > 1 then changed end), 0)
from v
where valid = 1
group by id
order by id;

You haven't indicated a specific product so this should work in any iso-compliant RDBMS

See this Fiddle demo

You can first find the ids whose first recorded color is red, and then find the aggregated color changes via count:

with cte as (
   select c1.* from (select c.id, min(c.date) dt from colors c group by c.id) c1
   join colors c2 on c2.id = c1.id and c2.date = c1.dt
   where c2.color = 'red' and c1.id <= date('2005-01-01')
)
select c.id, count(distinct c.color) color_changes from colors c 
where c.id in (select c2.id from cte c2) and c.date >= date('2005-01-01') and c.date <= date('2015-01-01')
group by c.id
union all
select c.id, 0 color_changes from colors c where c.date <= date('2005-01-01') and c.color = 'red' and c.id not in (select c2.id from cte c2)

See fiddle

So you want to intersect two sets; 1. The set of ids that were red on 2005-01-01, with 2. The set of ids with their transition count between 2005 & 2015.

First the reds on 2005,

Select id From my_table x
Where date = (Select max(date) from my_table y
              Where x.id=y.id and date <= DATE '2005-01-01')
    And color='red'

That's a standard correlated subquery.

The transition count is a little trickier, because your data contains some non-transitions (from 'red' to 'red') that must be ignored. You do this by ordering the records and comparing the prior record for same vs. different color with the Lag window function.

Select id, count(*) as color_changes
From (
    Select id, date,
        Case When color=Lag(color)
                  Over (Partition By ID Order by date)
             Then 'ignore' else 'count' end as transition 
    From my_table
    )
Where date between '2005-01-01' and '2015-01-01'
  And transition='count'
Group By id

Now Left Outer Join set 1 and set 2 and you're done.

Select A.id, B.color_changes From (
    Select id From my_table x
    Where date = (Select max(date) from my_table y
                  Where x.id=y.id and date <= DATE '2005-01-01')
        And color='red'
) A Left Outer Join (
    Select id, count(*) as color_changes
    From (
        Select id, date,
            Case When color=Lag(color)
                      Over (Partition By ID Order by date)
                 Then 'ignore' else 'count' end as transition 
        From my_table
        )
    Where date between '2005-01-01' and '2015-01-01'
      And transition='count'
    Group By id
) B On A.id=B.id
Order by A.id