-- CSE6242 / CS4803-DVA: Data and Visual Analytics Spring 2013
-- Homework 1 Question 3 example solutions

-- A
.separator ","

create table movies(id integer, name text, score integer);
.import movie-name-score.txt movies

create table cast(movie_id integer, cast_id integer, cast_name text);
.import movie-cast.txt cast

-- B
select count(distinct cast_id) from cast;

-- C
select count(*) from movies where score > 90;

-- D
select cast_name from cast
group by cast_id
order by count(*) desc
limit 5;

-- E
select cast_name from (movies, cast on id = movie_id)
group by cast_id 
order by avg(score) desc
limit 5;

/* The most common mistakes on Part F were forgetting to exclude
   self-pairs and forgetting to exclude repeated occurrences of two
   actors co-starring together.  

   For example, Hayden Christensen and Ewan McGregor co-star in two
   movies: Star Wars II and Star Wars III.  Without the check for
   distinctness, the pair (X,Y) would occur twice (once for each
   movie) in co_cast_members when it should only occur once (X and Y
   are their cast ids). */

-- F 
create view co_cast_members as
select 
  distinct  --no repeats
  a.cast_id as cast_member_1_id,
  b.cast_id as cast_member_2_id
from (cast as a, cast as b 
      on a.movie_id = b.movie_id 
      and a.cast_id <> b.cast_id);  --no self-pairs

/* A common error on Part G was naively joining with the CAST table:
   
     select ... 
     from co_cast_members, cast on cast_member_1_id = cast_id 
     ...

   This is done to map cast ids to cast names, but the issue is that a
   particular id<->name mapping can occur multiple times, once for
   each movie an actor acts in.  This causes the join to produce
   "extra" entries, inflating the co-cast counts for prolific actors.

   One solution is to first filter out repeats, as below.  Another
   common solution was to use COUNT(DISTINCT cast_member_2_id) instead
   of COUNT(*).  */

-- G
select cast_name, count(*) 
from (co_cast_members, (select distinct cast_name, cast_id from cast)
      on cast_member_1_id = cast_id)
group by cast_member_1_id
order by count(*) desc
limit 5;

/* Another common error in general was grouping by the wrong
   column. */


/* Many of you have discovered the trick of using GROUP BY to ensure
   distinctness, but be warned that the behavior of this trick is
   undefined in a certain way.  A (contrived) example is

     select cast_name, movie_id from cast group by cast_name;

   This produces a result in which any particular cast_name only
   appears once, but the particular movie_id that gets paired with
   that cast_name is undefined and can vary by implementation.  For
   example, which movie gets paired with Ewan McGregor?

   Adding more column names to the GROUP BY clause makes the filtering
   more permissive (instead of more constraining).

   http://stackoverflow.com/questions/1591909/group-by-behavior-when-no-aggregate-functions-are-present-in-the-select-clause
   http://stackoverflow.com/questions/2197693/any-reason-for-group-by-clause-without-aggregation-function
 */

