my code is trying to pull phone numbers from the raw data, CTE's have been created to find those with a phone number, without a phone number, those who have updated a phone number and separate mobile and home phone numbers. I know there are results to display but for some reason it shows no results even though the code is correct. Am i missing something/got the CTE's the wrong way round? I'm thinking I havent tried to pull information from my uncoverMobile CTE but cant figure out how to do it.
with rawphone as (
select
PARTITIONTIME,
event.CreatedOrUpdate.user.phoneNumbers,
event.CreatedOrUpdate.user.id as guid,
event.CreatedOrUpdate.user.permissions,
event.CreatedOrUpdate.user.requiredActions as actions,
kafkaData.topic,
cast(kafkaData.insertTime as timestamp) as updated_datetime,
cast(kafkaData.insertTime as date) as updated_date
from data-engineering-prod.landing_identity_secure.identity_users_v3
where
PARTITIONTIME >= '2022-01-18 00:00:00' --reporting starts from here
),
withoutphone as (
select *
from
rawphone
where
phoneNumbers is null
qualify row_number() over (partition by guid
order by updated_datetime desc) = 1
),
withphone as (
select *
from rawphone
where
phoneNumbers is not null
qualify row_number() over (partition by guid
order by updated_datetime asc) = 1
),
--return table of records where phone was not present, but now is
updatedphone as (
select withphone.*
from
withphone
inner join withoutphone
on withphone.guid = withoutphone.guid
where
withphone.updated_datetime > withoutphone.updated_datetime
),
unnestPermissions as (
select * from withphone,
unnest(permissions)
),
unnestphone as (
select * from updatedphone,
unnest(phoneNumbers) as phone
),
uncoverMobile as (
select distinct guid,
phone,
case when
left(phone, 4 )in ('+447')
then true
else false
end as mobile
from unnestphone
),
uncoveredAccountIDs as (
select distinct guid,
replace(concat(resourceId, resourceType), 'account', '') as account_id,
case when resourceId = 'account' or resourceType = 'account' then 'account' end as account
from unnestPermissions
),
phone_update as (
select
ue.updated_date,
ue.updated_datetime as phone_updated_datetime_utc,
uaid.account_id as account_no,
'phone_number_ovo_001' as action_code
from
updatedphone ue
left outer join
uncoveredAccountIDs uaid on uaid.guid = ue.guid
qualify row_number() over (partition by ue.guid, updated_date
order by updated_datetime desc) = 1
)
select
to_hex(md5(cast(coalesce(cast(account_no as
string
), '') || '-' || coalesce(cast(phone_updated_datetime_utc as
string
), '') || '-' || coalesce(cast(action_code as
string
), '') as
string
)))
as completed_action_id,
*
from phone_update