clustering_source_data.sql

SELECT origdestcitycode, origincitycode, destinationcitycode,
sum(JAN) /sum(seats) ::float as shareJAN,
sum(FEB) /sum(seats) ::float as shareFEB,
sum(MAR) /sum(seats) ::float as shareMAR,
sum(APR) /sum(seats) ::float as shareAPR,
sum(MAY) /sum(seats) ::float as shareMAY,
sum(JUN) /sum(seats) ::float as shareJUN,
sum(JUL) /sum(seats) ::float as shareJUL,
sum(AUG) /sum(seats) ::float as shareAUG,
sum(SEP) /sum(seats) ::float as shareSEP,
sum(OCT) /sum(seats) ::float as shareOCT,
sum(NOV) /sum(seats) ::float as shareNOV,
sum(DEC) /sum(seats) ::float as shareDEC,
sum(Oneway) /sum(seats) ::float as shareoneway,
sum(SUN) /sum(seats) ::float as shareSUN,
sum(MON) /sum(seats) ::float as shareMON,
sum(TUE) /sum(seats) ::float as shareTUE,
sum(WED) /sum(seats) ::float as shareWED,
sum(THU) /sum(seats) ::float as shareTHU,
sum(FRI) /sum(seats) ::float as shareFRI,
sum(SAT) /sum(seats) ::float as shareSAT,
sum(los_lessthan7) /sum(seats) ::float as sharelos_lessthan7,
sum(los_7andmore) /sum(seats) ::float as sharelos_7andmore,
sum(adv_lessthan7) /sum(seats) ::float as shareadv_lessthan7,
sum(adv_between7and30) /sum(seats) ::float as shareadv_between7and30,
sum(adv_morethan30) /sum(seats) ::float as shareadv_morethan30,
sum(solo) /sum(seats) ::float as sharesolo,
sum(couple) /sum(seats) ::float as sharecouple,
sum(atleastonechild) /sum(seats) ::float as shareatleastonechild

FROM(
	SELECT origdestcitycode, origincitycode, destinationcitycode, seats,
	case when to_char(outbounddate, 'mm') = '01' then seats end as JAN,
	case when to_char(outbounddate, 'mm') = '02' then seats end as FEB,
	case when to_char(outbounddate, 'mm') = '03' then seats end as MAR,
	case when to_char(outbounddate, 'mm') = '04' then seats end as APR,
	case when to_char(outbounddate, 'mm') = '05' then seats end as MAY,
	case when to_char(outbounddate, 'mm') = '06' then seats end as JUN,
	case when to_char(outbounddate, 'mm') = '07' then seats end as JUL,
	case when to_char(outbounddate, 'mm') = '08' then seats end as AUG,
	case when to_char(outbounddate, 'mm') = '09' then seats end as SEP,
	case when to_char(outbounddate, 'mm') = '10' then seats end as OCT,
	case when to_char(outbounddate, 'mm') = '11' then seats end as NOV,
	case when to_char(outbounddate, 'mm') = '12' then seats end as DEC,
	case when inbounddate is NULL then seats end as oneway,
	case when to_char(outbounddate, 'D') = '1' then seats end as SUN,
	case when to_char(outbounddate, 'D') = '2' then seats end as MON,
	case when to_char(outbounddate, 'D') = '3' then seats end as TUE,
	case when to_char(outbounddate, 'D') = '4' then seats end as WED,
	case when to_char(outbounddate, 'D') = '5' then seats end as THU,
	case when to_char(outbounddate, 'D') = '6' then seats end as FRI,
	case when to_char(outbounddate, 'D') = '7' then seats end as SAT,
	case when dayslengthofstay between 0 and 6 and dayslengthofstay is not NULL	then seats end as los_lessthan7,
	case when dayslengthofstay >= 7 and dayslengthofstay is not NULL	then seats end as los_7andmore,
	case when daystodeparture between 0 and 6 	then seats end as adv_lessthan7,
	case when daystodeparture between 7 and 30 	then seats end as adv_between7and30,
	case when daystodeparture >= 30 		then seats end as adv_morethan30,
	case when adults = 1 and children = 0 		then seats end as solo,
	case when adults = 2 and children = 0 		then seats end as couple,
	case when children > 0 				then seats end as atleastonechild

	FROM search_table
	-- taking 365 days of data removing the "dataapi" for consistency and making sure the data is complete
	WHERE date >= getdate() - 365 and origincitycode is not null and destinationcitycode is not null and platform in ( 'website', 'Website')
	)
	GROUP BY origdestcitycode, origincitycode, destinationcitycode
	-- keeping only OD with more than 10000 over the period
	HAVING sum(seats) >= 10000