8bc525e4c2
# Description This relates to the data alerts on resolutions models. We have one incident status that refers to the record having missing information. The record that is currently triggering this alerts is indeed tagged as such. The idea is to: * Create a new boolean that flags if the incident has missing information. This is propagated stg to reporting. * Apply a where clause to any existing data alert that might be susceptible to fail due to not having enough quality, in staging and reporting. * Remove over coverage in intermediate since it's already happening in reporting. However we keep a few not-null tests and the PK on critical information (i.e., id_booking). # Checklist - [X] The edited models and dependants run properly with production data. - [X] The edited models are sufficiently documented. - [X] The edited models contain PK tests, and I've ran and passed them. - [NA] I have checked for DRY opportunities with other models and docs. - [NA] I've picked the right materialization for the affected models. # Other - [ ] Check if a full-refresh is required after this PR is merged. Related work items: #31668
272 lines
9.2 KiB
SQL
272 lines
9.2 KiB
SQL
with
|
|
stg_resolutions__incidents as (
|
|
select * from {{ ref("stg_resolutions__incidents") }}
|
|
),
|
|
int_core__user_host as (select * from {{ ref("int_core__user_host") }}),
|
|
int_daily_currency_exchange_rates as (
|
|
select * from {{ ref("int_daily_currency_exchange_rates") }}
|
|
),
|
|
logs_timeline as (
|
|
select
|
|
id_incident,
|
|
(
|
|
select (comment ->> 'CreatedDate')::timestamp
|
|
from jsonb_array_elements(comment_logs::jsonb) comment
|
|
order by (comment ->> 'CreatedDate')::timestamp
|
|
limit 1
|
|
) as first_comment_at_utc,
|
|
(
|
|
select (comment ->> 'CreatedDate')::timestamp
|
|
from jsonb_array_elements(comment_logs::jsonb) comment
|
|
order by (comment ->> 'CreatedDate')::timestamp desc
|
|
limit 1
|
|
) as last_comment_at_utc,
|
|
(
|
|
select count(*) from jsonb_array_elements(comment_logs::jsonb) comment
|
|
) as comment_count,
|
|
(
|
|
select (status_step ->> 'CreatedDate')::timestamp
|
|
from jsonb_array_elements(status_history_logs::jsonb) status_step
|
|
order by (status_step ->> 'CreatedDate')::timestamp
|
|
limit 1
|
|
) as first_status_at_utc,
|
|
(
|
|
select (status_step ->> 'CreatedDate')::timestamp
|
|
from jsonb_array_elements(status_history_logs::jsonb) status_step
|
|
order by (status_step ->> 'CreatedDate')::timestamp desc
|
|
limit 1
|
|
) as last_status_at_utc
|
|
from stg_resolutions__incidents
|
|
),
|
|
-- The host can submit any number of damaged items in the damage report.
|
|
-- Each damaged item can have different currencies, so we need to convert
|
|
-- the amount to the host's currency.
|
|
damage_reported_items as (
|
|
select
|
|
i.id_incident,
|
|
jsonb_array_elements(i.damage_report_items::jsonb) as item,
|
|
uh.account_currency_iso4217 as host_currency,
|
|
i.created_date_utc
|
|
from stg_resolutions__incidents i
|
|
left join int_core__user_host uh on i.id_user_host = uh.id_user_host
|
|
where i.damage_report_items is not null
|
|
),
|
|
damage_report as (
|
|
select
|
|
dri.id_incident,
|
|
dri.host_currency,
|
|
count(distinct dri.item ->> 'Id') as number_damaged_items,
|
|
sum(
|
|
coalesce(
|
|
((dri.item ->> 'OriginalAmount')::numeric * cer.rate)::decimal(
|
|
19, 4
|
|
),
|
|
0
|
|
)
|
|
) as original_value_amount_in_host_currency,
|
|
sum(
|
|
coalesce(
|
|
((dri.item ->> 'OriginalAmount')::numeric * cer_gbp.rate)::decimal(
|
|
19, 4
|
|
),
|
|
0
|
|
)
|
|
) as original_value_amount_in_gbp,
|
|
sum(
|
|
coalesce(
|
|
((cm.corrective_measure ->> 'Amount')::numeric * cer.rate)::decimal(
|
|
19, 4
|
|
),
|
|
0
|
|
)
|
|
) as asked_repair_amount_in_host_currency,
|
|
sum(
|
|
coalesce(
|
|
(
|
|
(cm.corrective_measure ->> 'Amount')::numeric * cer_gbp.rate
|
|
)::decimal(19, 4),
|
|
0
|
|
)
|
|
) as asked_repair_amount_in_gbp,
|
|
sum(
|
|
coalesce(
|
|
(
|
|
(cm.corrective_measure ->> 'SettlementAmount')::numeric
|
|
* cer.rate
|
|
)::decimal(19, 4),
|
|
0
|
|
)
|
|
) as accepted_amount_in_host_currency,
|
|
sum(
|
|
coalesce(
|
|
(
|
|
(cm.corrective_measure ->> 'SettlementAmount')::numeric
|
|
* cer_gbp.rate
|
|
)::decimal(19, 4),
|
|
0
|
|
)
|
|
) as accepted_amount_in_gbp
|
|
from damage_reported_items dri
|
|
left join
|
|
lateral jsonb_array_elements(
|
|
case
|
|
when jsonb_typeof(dri.item -> 'CorrectiveMeasures') = 'array'
|
|
then dri.item -> 'CorrectiveMeasures'
|
|
else '[]'::jsonb
|
|
end
|
|
) as cm(corrective_measure)
|
|
on true
|
|
left join
|
|
int_daily_currency_exchange_rates cer
|
|
on dri.created_date_utc = cer.rate_date_utc
|
|
and (cm.corrective_measure ->> 'Currency') = cer.from_currency
|
|
and dri.host_currency = cer.to_currency
|
|
left join
|
|
int_daily_currency_exchange_rates cer_gbp
|
|
on dri.created_date_utc = cer_gbp.rate_date_utc
|
|
and (cm.corrective_measure ->> 'Currency') = cer_gbp.from_currency
|
|
and cer_gbp.to_currency = 'GBP'
|
|
group by dri.id_incident, dri.host_currency
|
|
),
|
|
guest_amounts_in_gbp as (
|
|
select
|
|
i.id_incident,
|
|
(i.guest_contribution_amount_in_txn_currency * cer.rate)::decimal(
|
|
19, 4
|
|
) as guest_contribution_amount_in_gbp
|
|
from stg_resolutions__incidents i
|
|
left join
|
|
int_daily_currency_exchange_rates cer
|
|
on (i.calculation_at_utc)::date = cer.rate_date_utc
|
|
and cer.from_currency = i.guest_contribution_currency
|
|
and cer.to_currency = 'GBP'
|
|
)
|
|
|
|
select
|
|
-- Basic Incident Details
|
|
i.id_incident,
|
|
i.id_verification,
|
|
i.current_status_name,
|
|
i.is_incident_missing_details,
|
|
i.is_submission_complete,
|
|
i.current_agent_name,
|
|
i.document_version,
|
|
i.created_at_utc,
|
|
i.created_date_utc,
|
|
i.updated_at_utc,
|
|
i.updated_date_utc,
|
|
|
|
-- Resolution Details
|
|
lt.first_comment_at_utc,
|
|
lt.last_comment_at_utc,
|
|
lt.comment_count,
|
|
case
|
|
when lt.first_comment_at_utc is not null
|
|
then
|
|
(
|
|
extract(epoch from (lt.first_comment_at_utc - i.created_at_utc)) / 3600
|
|
)::decimal(19, 4)
|
|
else null
|
|
end as time_to_first_comment_in_hours,
|
|
lt.first_status_at_utc,
|
|
lt.last_status_at_utc,
|
|
case
|
|
when i.is_submission_complete
|
|
then
|
|
(
|
|
extract(epoch from (lt.last_status_at_utc - lt.first_status_at_utc))
|
|
/ 3600
|
|
)::decimal(19, 4)
|
|
else null
|
|
end as resolution_time_in_hours,
|
|
dr.number_damaged_items,
|
|
dr.original_value_amount_in_host_currency,
|
|
dr.original_value_amount_in_gbp,
|
|
dr.asked_repair_amount_in_host_currency,
|
|
dr.asked_repair_amount_in_gbp,
|
|
dr.accepted_amount_in_host_currency,
|
|
dr.accepted_amount_in_gbp,
|
|
dr.host_currency,
|
|
|
|
-- Resolutions calculator
|
|
i.protection_name,
|
|
i.waiver_or_deposit_name,
|
|
i.guest_verification_status,
|
|
i.lower_protection_limit_usd,
|
|
i.upper_protection_limit_usd,
|
|
i.calculated_payout_amount_in_txn_currency,
|
|
i.calculated_payout_currency,
|
|
(i.calculated_payout_amount_in_usd * cer.rate)::decimal(
|
|
19, 4
|
|
) as calculated_payout_amount_in_gbp,
|
|
i.submitted_payout_amount_in_txn_currency,
|
|
i.submitted_payout_currency,
|
|
(i.submitted_payout_amount_in_usd * cer.rate)::decimal(
|
|
19, 4
|
|
) as submitted_payout_amount_in_gbp,
|
|
i.calculated_guest_charge_amount_in_txn_currency,
|
|
i.calculated_guest_charge_currency,
|
|
(i.calculated_guest_charge_amount_in_usd * cer.rate)::decimal(
|
|
19, 4
|
|
) as calculated_guest_charge_amount_in_gbp,
|
|
i.submitted_guest_charge_amount_in_txn_currency,
|
|
i.submitted_guest_charge_currency,
|
|
(i.submitted_guest_charge_amount_in_usd * cer.rate)::decimal(
|
|
19, 4
|
|
) as submitted_guest_charge_amount_in_gbp,
|
|
|
|
-- Host Details
|
|
i.id_user_host,
|
|
i.host_account_name,
|
|
|
|
-- Host Contact Details
|
|
i.host_email,
|
|
i.host_last_name,
|
|
i.host_first_name,
|
|
i.host_phone_code,
|
|
i.host_phone_number,
|
|
|
|
-- Guest Details
|
|
i.id_user_guest,
|
|
i.guest_email,
|
|
i.guest_last_name,
|
|
i.guest_first_name,
|
|
i.guest_phone_code,
|
|
i.guest_phone_number,
|
|
|
|
-- Guest Deposit Details
|
|
i.is_guest_deposit_retained,
|
|
i.is_guest_deposit_collected,
|
|
i.deposit_retained_amount_in_txn_currency,
|
|
i.deposit_retained_currency,
|
|
|
|
-- Guest Involvements
|
|
i.has_guest_contributed_to_cost,
|
|
i.has_host_taken_preventative_steps,
|
|
i.guest_contribution_amount_in_txn_currency,
|
|
i.guest_contribution_currency,
|
|
ga.guest_contribution_amount_in_gbp,
|
|
i.is_guest_contacted_about_damage,
|
|
|
|
-- Accommodation Details
|
|
i.id_accommodation,
|
|
i.accommodation_name,
|
|
|
|
-- Booking Details
|
|
i.check_in_at_utc,
|
|
i.check_in_date_utc,
|
|
i.check_out_at_utc,
|
|
i.check_out_date_utc,
|
|
i.id_booking,
|
|
i.booking_status,
|
|
i.id_reservation
|
|
|
|
from stg_resolutions__incidents i
|
|
left join logs_timeline lt on i.id_incident = lt.id_incident
|
|
left join damage_report dr on i.id_incident = dr.id_incident
|
|
left join guest_amounts_in_gbp ga on i.id_incident = ga.id_incident
|
|
left join
|
|
int_daily_currency_exchange_rates cer
|
|
on (i.calculation_at_utc)::date = cer.rate_date_utc
|
|
and cer.from_currency = 'USD'
|
|
and cer.to_currency = 'GBP'
|