select * from people;

select * from weather;
----------------------------------- EXTENDED SNAPSHOT REDUCIBILITY -------------------------------------------

with p as (select lineage ts us, te ue, * from people)
select *, ue-us duration from (p align weather on ue-us>5) u;


-------------------------------------------- DISTINCT --------------------------------------------------------
with pNp as (select * from (people normalize people as p on people.name=p.name) n)
select distinct lineage conf dest, ts, te
from pNp;

--------------------------------------- CARTESIAN PRODUCT  ---------------------------------------------------
with pAw as (select * from (people align weather on dest=loc) u),
     wAp as (select * from (weather align people on dest=loc) u)
select  lineage conf pAw.dest, wAp.weath, wAp.ts, wAp.te
from pAw, wAp
where dest=loc and pAw.ts=wAp.ts and pAw.te=wAp.te;

with pAw as (select * from (people align weather on dest=loc) u),
     wAp as (select * from (weather align people on dest=loc) u),
     pJw as (select name,dest,weath,pAw.ts,pAw.te from pAw, wAp where dest=loc and pAw.ts=wAp.ts and pAw.te=wAp.te)
select distinct lineage dest,ts,te
from (pJw normalize pJw as k on pJw.dest = k.dest)u;

with pAw as (select * from (people align weather on dest=loc) u),
     wAp as (select * from (weather align people on dest=loc) u),
     pJw as (select name,dest,weath,pAw.ts,pAw.te from pAw, wAp where dest=loc and pAw.ts=wAp.ts and pAw.te=wAp.te)
select lineage *
from (pJw normalize pJw as k on pJw.dest = k.dest)u;

---------------- CARTESIAN PRODUCT ~ EXTENDED SNAPHOT REDUCIBILITY  ------------------------------------------
with p as (select lineage ts us, te ue, * from people),
     pAw as (select * from (p align weather on dur(us,ue)<3) u),
     wAp as (select * from (weather align p on dur(us,ue)<2) u)
select lineage conf *
from pAw, wAp
where dest=loc and pAw.ts=wAp.ts and pAw.te=wAp.te;


--------------------------------------- AGGREGATION/GROUP BY -------------------------------------------------

-- (shows the BEST case scenario for the number of people that were in a city at every point in time)
with pNp as (select * from (people normalize people as p on people.dest=p.dest) n)
select conf lineage dest, ts, te, count(*)
from pNp
group by dest, ts, te;

------------------------------------------- SET OPERATIONS ----------------------------------------------------

-- a) Union (all locations for which either there is a prediction or someone might have visited them
with pNw as (select * from (people normalize weather on dest=loc) u),
     wNp as (select * from (weather normalize people on dest=loc) u)
select lineage conf dest, ts, te from pNw 
union
select loc, ts, te from wNp;

-- b) Difference: (locations that were visited but there was no weather prediction for them)
select lineage conf dest, ts, te from (people normalize weather on dest=loc) n
except
select loc, ts, te from (weather normalize people on dest=loc) n;


-- c) Intersect: (locations that were visited and for whose weather a prediction has been made)
select lineage conf dest, ts, te from (people normalize weather on dest=loc) n
intersect
select loc, ts, te from (weather normalize people on dest=loc) n;


--------------------------------------- TEMPORAL PROCESSES --------------------------------------------
-- Normalize people on people
select * from (people normalize people as p on people.dest=p.dest) n;

-- Normalize weather on weather
select * from (weather normalize weather as w on weather.loc=w.loc) n;

-- Normalize people on weather
select * from (people normalize weather on people.dest=weather.loc) n;

-- Normalize weather on people
select * from (weather normalize people on weather.loc=people.dest) n;

-- Align people on weather
select * from (people align weather on dest=loc) u;

-- Align weather on people
select * from (weather align people on dest=loc) u;
