--- hint in: http://theparticle.com/cs/bc/dbsys/20211019.txt
--- creates daily_prcnt_1


--- Pearson correlation from https://en.wikipedia.org/wiki/Pearson_correlation_coefficient
with avgprcnt as ( 
   select a.*, 
      avg(cast(prcnt as real)) over (partition by symbol) avg_prcnt,
      stddev(cast(prcnt as real)) over (partition by symbol) sd_prcnt
   from daily_prcnt_1 a
   where symbol < 'AC'    --don't run this algo for entire day.... only run it for a few symbols.
),
pearson as (
   select a.symbol s1, b.symbol s2, 
    avg( (cast(a.prcnt as real) - a.avg_prcnt) * ( cast(b.prcnt as real) - b.avg_prcnt ) ) / ( a.sd_prcnt*b.sd_prcnt) p 
   from avgprcnt a
      inner join avgprcnt b
      on a.tdate=b.tdate 
   where a.symbol <= b.symbol
   group by a.symbol,b.symbol, a.sd_prcnt, b.sd_prcnt
)
select *
from pearson
;

---pretend we bought $1000 of INTC in 1999-01-01, how much would we have in 2008-01-01 ?

select exp( sum( log( 1 + prcnt/100.0 ) ) ) gains
from daily_prcnt_1
where symbol='INTC' and tdate between '1999-01-01' and '2008-01-01'



with recursive blah (n) as (
   select 1 n   --- base case
   union all 
   select n + 1 as n   --  recursive case
   from blah 
   where n < 10      -- termination criteria
)
select * 
from blah;

create table employee(eid bigint, mid bigint);

insert into employee 
 with recursive blah (n) as (
   select 1 n   --- base case
   union all 
   select n + 1 as n   --  recursive case
   from blah 
   where n < 10      -- termination criteria
 )
 select n, case when n = 1 then null else n-1 end 
 from blah;


---find everyone who works for employee 5  (directly or indirectly)

with recursive emp (eid) as (
   select eid                  -- direct reports to emp 5
   from employee where mid=5   --employee 5 is the manager
   union all 
   select a.eid                --- employees
   from employee a 
      inner join emp b         --- whose manager is one of the employees previously pulled.
      on a.mid = b.eid
)
select * from emp;

--- display "path" to each employee.
with recursive emp (s,eid) as (
   select concat('/',eid) s, eid from employee where mid is null -- very very top, no manger.
   union all
   select concat( b.s, '/', a.eid ) s, a.eid
   from employee a 
     inner join emp b
     on a.mid = b.eid
)
select s 
from emp;