-- Exercise 3.18a

    create table buckets (bucket int, primary key(bucket));
    insert into buckets select distinct floor(dollars/500) from orders;
    select bucket*500 as "range-start", sum(dollars) from orders o, buckets b
       where floor(o.dollars/500) = b.bucket
       group by b.bucket;
    drop table buckets;

-- Exercise 3.19b
--cumulative histogram of dollars figures
--the median is where sum(ct) = (select count(*)/2 from orders)
    select h2.dollars, sum(ct) from
      (select dollars, count(dollars) ct from orders group by dollars) h1,
      (select dollars from orders group by dollars) h2
       where h1.dollars <= h2.dollars   -- over all h1 vals <= h2 vals
       group by h2.dollars;
       order by h2.dollars;  -- just for reporting

--the median in one query, where F here is the cumulative histogram
--computed above
  select dollars from
    (select h2.dollars, sum(h1.ct) ct from
      (select dollars, count(dollars) ct from orders group by dollars) h1,
      (select dollars from orders group by dollars) h2
        where h1.dollars <= h2.dollars   -- over all h1 vals <= h2 vals
        group by h2.dollars) F
      where ct = (select min(ct1) from 
        (select h2.dollars, sum(ct1) ct1 from
        (select dollars, count(dollars) ct1 from orders group by dollars) h1,
        (select dollars from orders group by dollars) h2
          where h1.dollars <= h2.dollars   -- over all h1 vals <= h2 vals
          group by h2.dollars) F
        where ct1 >= (select count(*)/2 from orders));


