some fun sql query highlights

heartrate.sql

CREATE OR REPLACE FUNCTION heartrate()
RETURNS TABLE(nodeid integer, hrbpm numeric) AS
$BODY$
DECLARE r record;
    BEGIN
    FOR r IN SELECT DISTINCT wampum.nodeid FROM wampum ORDER BY nodeid
    LOOP
        RETURN QUERY SELECT wampum.nodeid, wampum.hrbpm FROM wampum
        WHERE wampum.nodeid=r.nodeid ORDER BY id DESC LIMIT 1;
    END LOOP;
    RETURN;
    END
$BODY$
LANGUAGE 'plpgsql' ;

hs.sql

with early as (     
    select sid, min(timestamp)     
    from thinkcspy_log, courses     
    where courses.course_name = thinkcspy_log.course_id     
    and courses.inst_type =  'highschool'     
    group by sid 
)
select thinkcspy_log.sid, toc.chapter_id 
from early, thinkcspy_log, toc 
where early.sid = thinkcspy_log.sid 
and early.min = thinkcspy_log.timestamp 
and toc.div_id = thinkcspy_log.div_id;

hs_diff.sql

select sid, min(timestamp), max(timestamp)
from thinkcspy_log, courses
where courses.course_name = thinkcspy_log.course_id
and courses.inst_type =  'highschool'
group by sid;