How to count changes within each column and in SQL

Solution 1:

Can this work for you?

WITH
-- your input, do not use in query ...
indata(id,city,addr,steps,dt) AS (
          SELECT 1,NULL     ,NULL             ,'a',DATE '2021-11-01'
UNION ALL SELECT 1,'NY'     ,NULL             ,'b',DATE '2021-11-04'
UNION ALL SELECT 1,'Chicago',NULL             ,'c',DATE '2021-11-05'
UNION ALL SELECT 2,'SF'     ,'33, ABC colony' ,'x',DATE '2021-12-01'
UNION ALL SELECT 2,'SF'     ,'33, ABC colony' ,'y',DATE '2021-12-04'
UNION ALL SELECT 2,'SF'     ,'44, Kang Street','z',DATE '2021-12-05'
UNION ALL SELECT 3,'Austin' ,NULL             ,'i',DATE '2022-01-01'
UNION ALL SELECT 3,'Austin' ,'12, Bridgetown' ,'j',DATE '2022-01-04'
UNION ALL SELECT 3,'Austin' ,NULL             ,'k',DATE '2022-01-05'
)
-- end of your input
-- real query starts here, replace following comma with "WITH" ...
,
olap AS (
  SELECT
    id
  -- a NULL is not COUNTed DISTINCT, but an empty string is
  , CASE WHEN city IS NULL AND LAG(city) OVER w IS NOT NULL THEN '' ELSE city END AS city
  , CASE WHEN addr IS NULL AND LAG(addr) OVER w IS NOT NULL THEN '' ELSE addr END AS addr
  FROM indata
  WINDOW w AS (PARTITION BY id ORDER BY dt)
)
SELECT
  id
, GREATEST(COUNT(DISTINCT city),COUNT(DISTINCT addr)) AS changecount
FROM olap
GROUP BY 1
ORDER BY 1
;
-- out  id | changecount 
-- out ----+-------------
-- out   1 |           2
-- out   2 |           2
-- out   3 |           2