-----------------HW5 hint-------------------------------------------------------------
with prevclose as (
  select a.tdate,a.symbol,a.close,
   lag(a.close) over (partition by a.symbol order by a.tdate) pclose, 
   b.pre, b.post, c.amount
  from cts a 
    left outer join splits b 
    on a.tdate=b.tdate and a.symbol=b.symbol
    left outer join dividends c
    on a.tdate=c.tdate and a.symbol=c.symbol
  where a.symbol='MSFT'
 )
 select a.*, 
   100.0*(close - pclose)/pclose as prcnt   --TODO: adjust for splits/dividends 
 from prevclose a
 where pclose>0 and 
  ( ( tdate between '2004-11-12' and '2004-11-16') or
    ( tdate between '2003-02-13' and '2003-02-19') )
order by tdate,symbol
;

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



hw3: doorlog(eventid,doorid,tim,username,event)
--- event is "E" for entry, and "X" for exit.

q3: If doorid=1 is front entrance door, and doorid=3 is back entrance door, and these are the only doors in the building, how many people are currently in the building?

select sum(case when event='E' then 1
                when event='X' then -1 
                else 0 
           end) cnt
from doorlog
where doorid in (1,3)

---------------------------------------------
select (select count(*) from doorlog where doorid in (1,3) and event='E')-
       (select count(*) from doorlog where doorid in (1,3) and event='X');


SQL = DDL/DML; create table & select

functions; substr, concat, etc.
case statements

aggregate: sum/avg/stddev/count 

windowing functions: same as aggregates, lag/lead/last_value/rank/row_number/etc

JOINS

inner join
left outer join 
full outer join 
cross join 

natural inner join  -- avoid using in production; ok for adhoc 


lets come up with some domain: library


book(bookid, title, isbn) 

subject(subjectid, description)
booksubject(bookid, subjectid)
author(authorid, fname,lname,dob)
bookauthor(bookid, authorid)

customer(custid, fname, lname, dob, ssn)

bookpurchase(purchid, bookid, price, tim)
bookdestroyed(destid, bookid, tim)
bookborrowed(bid, bookid, custid, tim)
bookreturned(bid, tim)


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

-- how many customers does library have?

select count(*) from customer;

-- what percentage of customers are teenagers (>12 and < 20 ).

select 100.0 * sum(case when extract(year from age(dob)) < 20 and extract(year from age(dob)) > 12 then 1.0 else 0.0 end) / sum(1.0) 
from customer


-- how many books are in the library (each copy counts as separate book; ignore lending).

select (select count(*) from bookpurchase) - (select count(*) from bookdestroyed);

with books as (
  select 1 n from bookpurchase
  union 
  select -1 from bookdestroyed
)
select sum(n)
from books

-------------------------------------------------------------------
--- how many books written by DNA are currently in the library [consider lended out books]

author(authorid, fname,lname,dob)
bookauthor(bookid, authorid)

bookpurchase(purchid, bookid, price, tim)
bookdestroyed(destid, bookid, tim)
bookborrowed(bid, bookid, custid, tim)
bookreturned(bid, tim)

with stats as (
 select 
    count(c.purchid) purchases, 
    count(d.destid) destroyed,
    count(e.bid) borrowed,
    count(f.bid) returned
 from author a
  inner join bookauthor b
  on a.authorid=b.authorid
  left outer join bookpurchase c
  on b.bookid=c.bookid
  left outer join bookdestroyed d
  on b.bookid=d.bookid
  left outer join bookborrowed e
  on b.bookid=e.bookid
  left outer join bookreturned f
  on e.bid=f.bid
 where a.fname='Douglas' and 
   a.lname='Adams' --- DNA
) 
select purchases - destroyed - borrowed + returned as cnt
from stats
;


book(bookid, title, isbn) 
subject(subjectid, description)
booksubject(bookid, subjectid)
author(authorid, fname,lname,dob)
bookauthor(bookid, authorid)
customer(custid, fname, lname, dob, ssn)
bookpurchase(purchid, bookid, price, tim)
bookdestroyed(destid, bookid, tim)
bookborrowed(bid, bookid, custid, tim)
bookreturned(bid, tim)

---how many books did customer=1234 borrowed but not returned?

select count(*)
from bookborrowed a
  left outer join bookreturned b
  on a.bid=b.bid
where a.custid=1234 and b.bid is null

--- get titles of all books customer=1234 borrowed but not returned?

select distinct c.title
from bookborrowed a
  left outer join bookreturned b
  on a.bid=b.bid
  inner join book c
  on a.bookid=c.bookid
where a.custid=1234 and b.bid is null


--- get all book titles with more than 3 authors

select b.title
from bookauthor a
  inner join book b
  on a.bookid=b.bookid
group by bookid
having count(*)>3
;

--- get all book titles for books with fewer than 2 authors (tip: consider books with no authors)

select a.title
from book a
  left outer join bookauthor b
  on a.bookid=b.bookid
group by a.bookid,a.title
having count(b.bookid)<2
;


; or / or go



book(bookid, title, isbn) 
subject(subjectid, description)
booksubject(bookid, subjectid)
author(authorid, fname,lname,dob)
bookauthor(bookid, authorid)
customer(custid, fname, lname, dob, ssn)
bookpurchase(purchid, bookid, price, tim)
bookdestroyed(destid, bookid, tim)
bookborrowed(bid, bookid, custid, tim)
bookreturned(bid, tim)

---how much money did library spend on purchasing books in 2020?

select sum(price)
from bookpurchase
where tim>='2020-01-00' and tim<'2021-01-01'
 --- extract(year from tim) = 2020


----- maximum tricky-------------------
--- find date when the maximum books were outstanding 

--- March 1 borrowed,   total borrowed=1
--- match 2, borrowed, total borrowed=2
--- match 3, return,   total borrowed=1
--- match 5, return,   total borrowed=0

---------------------------------------
for each date, find how many books are outstanding (borrowed but not returned).
then find max 
then find date that has that max.

with borrowedbydate as (
  select sum(1) c, cast(tim as date) d from bookborrowed group by cast(tim as date)
),
retbydate as (
  select sum(-1) c, cast(tim as date) d from bookreturned group by cast(tim as date)
),
deltabydate as (
  select coalesce(a.d,b.d) as d, coalesce(a.c,0) - coalesce(b.c,0) delta
  from borrowedbydate a
    full outer join retbydate b
    on a.d=b.d
),
runningtotal as (
  select d, delta, sum(delta) over(order by d 
     rows between unbounded preceding and current row ) tot   -- running total
  from deltabydate
),
maxtot as (
  select a.*, max(tot) over () maxtot    --maximum of the running totals
  from runningtotal
)
select d             -- date when maxtot=tot
from maxtot
where tot=maxtot;    --pick a record with the maximum running total



----------------------------------------------------------------------------
-- find state with maximum customers: customer(cid, name, state, zip...)
--- 

with cnts as (
  select state, count(*) cnt
  from customer
  group by state
),
mx as (
  select max(cnt) maxcnt from cnts
)
select *
from cnts cross join mx
where cnt = maxcnt


  select state, count(*) cnt, 
     max( count(*) ) over () maxcnt
  from customer
  group by state

  select state,
     max( sum(1) ) over () maxcnt
  from customer
  group by state