-
Notifications
You must be signed in to change notification settings - Fork 1
/
dim_households.sql
80 lines (49 loc) · 2.11 KB
/
dim_households.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
{{ config(schema="domestic_heating", materialized="table") }}
with
off_gas_postcodes as (
select postcode from {{ ref('stg_xoserve__off_gas_postcodes') }}
),
nsul as (
select
uprn,
local_authority_district_code,
local_authority_district_name
from {{ ref('stg_nsul__addresses') }}
),
epc_features as (
select * from {{ ref('base_domestic_heating__epc_features') }}
),
sales as (
select * from {{ ref("base_domestic_heating__ppd_matched_sales") }}
),
hpi as (
select * from {{ ref("base_domestic_heating__hpi") }}
),
final as (
select
epc_features.* except (postcode),
epc_features.wall_type = 'solid' as is_solid_wall,
case
when epc_features.heating_system = 'boiler_gas' then false
else off_gas_postcodes.postcode is not null
end as is_off_gas_grid,
epc_features.property_type not in (
'flat', 'park home'
) and epc_features.built_form != 'mid_terrace' as is_heat_pump_suitable_archetype,
nsul.local_authority_district_code as local_authority_district_code_2021,
nsul.local_authority_district_name as local_authority_district_name_2020,
case
when epc_features.property_type = 'flat' then sales.price * hpi.flat_price_factor
when epc_features.built_form = 'detached' then sales.price * hpi.detached_price_factor
when epc_features.built_form = 'semi_detached' then sales.price * hpi.semi_detached_price_factor
when contains_substr(epc_features.built_form, 'terrace') then sales.price * hpi.terraced_price_factor
else sales.price * hpi.price_factor
end as current_property_value_gbp
from epc_features
left join off_gas_postcodes on epc_features.postcode = off_gas_postcodes.postcode
left join nsul on nsul.uprn = epc_features.uprn
left join sales on epc_features.address_cluster_id = sales.address_cluster_id
left join hpi on nsul.local_authority_district_code = hpi.area_code
and date_trunc(sales.date_of_transfer, month) = date_trunc(hpi.period, month)
)
select * from final