ranking_example.sql

-- define reference categories and suitable listing ages
-- IRL would be a static table

drop table  if exists  #category;

select 
location_level_0_id
,location_level_1_id
,housing_type_id
,offering_type_id
,location_level_0_id || '_' || location_level_1_id || '_' || housing_type_id || '_' || offering_type_id as category_code
,case 
	when housing_type_id=1 and offering_type_id=2 then 3
	when housing_type_id=35 and offering_type_id=2 then 7
	when offering_type_Id=1 then 10
	else 5 end as category_max_lifetime
into #category
from atomic.ae_propertyfinder_interactions_1
where hostname like '%esimsar%'
group by 1,2,3,4,5;


----------------------------------------------------------------
----------------------------------------------------------------

---- Input 1: Impressions share per webID 
---- based on category total

----------------------------------------------------------------
----------------------------------------------------------------

-- category total in past week

drop table if exists #category_impressions;

select 
location_level_0_id || '_' || location_level_1_id || '_' || housing_type_id || '_' || offering_type_id as category_code
,sum(case when interaction_type='impression' then 1 else 0 end) as category_impressions
into #category_impressions
from atomic.ae_propertyfinder_interactions_1
where hostname like '%esimsar%'
and root_tstamp>=getdate()-7
group by 1;

-- webid total impressions in past week

drop table if exists #webid_impressions;

select 
web_id
,location_level_0_id || '_' || location_level_1_id || '_' || housing_type_id || '_' || offering_type_id as category_code
,sum(case when interaction_type='impression' then 1 else 0 end) as webid_impressions
into #webid_impressions
from atomic.ae_propertyfinder_interactions_1
where hostname like '%esimsar%'
and root_tstamp>=getdate()-7
group by 1,2;


-- join category total to webid total and find webid impression share

drop table if exists #INPUT_1_IMPRESSION_SHARE;

select
w.web_id
,w.category_code
,w.webid_impressions
,c.category_impressions
,cast(w.webid_impressions as float) / cast(c.category_impressions as float) as impression_share
into #INPUT_1_IMPRESSION_SHARE
from #webid_impressions w
join #category_impressions c on c.category_code=w.category_code
order by 2,5 desc;


----------------------------------------------------------------
----------------------------------------------------------------

---- Input 2: Listing age penalty

----------------------------------------------------------------
----------------------------------------------------------------


-- table with webids and their published date i.e. the date that
-- the listing went frontend
-- IRL will come from property history preservaton table, the below 
-- is an illustration for poc purposes

drop table if exists #webid_first_published;

select 
web_id
,location_level_0_id || '_' || location_level_1_id || '_' || housing_type_id || '_' || offering_type_id as category_code
,min(root_tstamp) as first_published
into #webid_first_published
from atomic.ae_propertyfinder_interactions_1
where hostname like '%esimsar%'
group by 1,2;

-- join with category table which has acceptable age and find the
-- time difference between first frontend and now, subtrracting the acceptable time period

drop table if exists #INPUT_2_AGE_PENALTY;

select
web_id
,datediff('days',first_published, getdate()) - category_max_lifetime as age_penalty
into #INPUT_2_AGE_PENALTY
from #webid_first_published p
join #category c on c.category_code=p.category_code;



----------------------------------------------------------------
----------------------------------------------------------------

---- Input 3: Quality Score Penalty

----------------------------------------------------------------
----------------------------------------------------------------


-- current quality score penality - webID taken in most recent state
-- (100-qa)*scale_factor
-- IRL will come from property table, not snowplow as below is
-- illustrative only

drop table if exists #scale_factor;

select 
0.25 as scale_factor
into #scale_factor;


select i.web_id, qs, (100- qs)*scale_factor as qs_penalty
into #INPUT_3_QS_PENALTY
from atomic.ae_propertyfinder_interactions_1 i
join
	(select  web_id, max(root_tstamp) as root_tstamp
	from atomic.ae_propertyfinder_interactions_1
	where hostname like '%esimsar%'
	group by 1
	) as a on a.web_id=i.web_id and a.root_tstamp=i.root_tstamp
cross join #scale_factor
group by 1,2,3;



select 
t1.web_id
,qs_Penalty
,case when age_penalty<0 then 0 else age_penalty end as age_penalty
,isnull(impression_share,0) as impression_share
,((isnull(impression_share,0))*(-1)*(case when age_penalty<0 then 0 else age_penalty end)) - qs_Penalty as ranking_score
into atomic.dm_ranking_example
from #INPUT_3_QS_PENALTY t1
join #INPUT_2_AGE_PENALTY t2 on t1.web_id=t2.web_id
left join #INPUT_1_IMPRESSION_SHARE t3 on t3.web_id=t1.web_id
order by 4 desc