CODEWITHSUNDEEP
sql
Jwan622
Jwan622

Reputation: 11659

How to use sql to build an audit table (all changes that happen to a specific table). Sql help needed

Say I have a user table that looks like this:

user_id received_at preference_1 preference_2 preference_3
1 10:00 null dog burger
2 10:05 lake dog burger
3 10:06 mountain cat pizza
1 10:07 mountain dog pizza
2 10:08 lake dog pizza

I want to write sql that basically creates another table that lists the changes like this:

user_id received_at preference_1_change preference_2_change preference_3_change
1 10:07 change no_change change
2 10:08 no_change no_change change

How does one do this?

I have something like this:

with user_states as (
    select user_id,
           received_at,
           coalesce(preference_1, '_null_') as preference_1,
           coalesce(preference_2, '_null_') as preference_2,
           coalesce(preference_3, '_null_') as preference_3,
    from <table>
    where user_id = '1'
), user_changes as (
select row_number() OVER (PARTITION BY user_id ORDER BY received_at ASC) as user_row_number,
       user_id,
       received_at,
       lag(preference_1, 1) OVER (PARTITION BY user_id ORDER BY received_at ASC) prev_preference_1,
       lag(preference_2, 1) OVER (PARTITION BY user_id ORDER BY received_at ASC) prev_preference_2,
       lag(preference_3, 1) OVER (PARTITION BY user_id ORDER BY received_at ASC) prev_preference_3,
       preference_1 as curr_preference_1,
       preference_2 as curr_preference_2,
       preference_3 as curr_preference_3,
       case when coalesce(LAG(preference_1, 1) OVER (PARTITION BY user_id ORDER BY received_at ASC), '_null_') = preference_1 then 'no change' else 'change' end as preference_1_change,
       case when coalesce(LAG(preference_2, 1) OVER (PARTITION BY user_id ORDER BY received_at ASC), '_null_') = preference_2 then 'no change' else 'change' end as preference_2_change,
       case when coalesce(LAG(preference_3, 1) OVER (PARTITION BY user_id ORDER BY received_at ASC), '_null_') = preference_3 then 'no change' else 'change' end as preference_3_change
from user_states
)
select  user_row_number, user_id, received_at, preference_1_change, preference_2_change, preference_3_change, prev_preference_1, curr_preference_1, prev_preference_2, curr_preference_2, curr_preference_3, prev_preference_3
from    user_changes
order by user_row_number ASC
;

but that's only for 1 user... how do I do this for all users? Do you see my problem?

Upvotes: 0

Views: 40

Answers (1)

The Impaler
The Impaler

Reputation: 48865

You didn't mention the database so I assumed it was PostgreSQL.

You can use LAG() to peek at the previous row according to a specific ordering criteria.

For example:

select
  user_id,
  received_at,
  case when pref_1 <> prev1 then 'change' else 'no_change' end as pref1_chg,
  case when pref_2 <> prev2 then 'change' else 'no_change' end as pref2_chg,
  case when pref_3 <> prev3 then 'change' else 'no_change' end as pref3_chg
from (
  select *,
    lag(pref_1) over w as prev1,
    lag(pref_2) over w as prev2,
    lag(pref_3) over w as prev3
  from t
  window w as (partition by user_id order by received_at)
) x
where pref_1 <> prev1
   or pref_2 <> prev2
   or pref_3 <> prev3

Note: If you want to account for nulls -- a change from null to a value or vice versa -- you can replace <> by is distinct from.

Upvotes: 1

Related Questions