
--- a simple three-dimensional fact table generated from
--- our movie database
--- Faking it with views ... 

--- use your imagination! 

drop view if exists facts;
drop view if exists type_facts;
drop view if exists genre_facts;
drop view if exists year_facts;
drop view if exists year_genre_facts;
drop view if exists year_genre_facts_facts;
drop view if exists type_year_facts;
drop view if exists type_genre_facts;
drop view if exists all_facts;


--- a three-dimensional fact table 
create view all_facts as 
select
  year,              --- first dimension 
  type,              --- second dimension 
  g.genre as genre,  --- third dimension 
  count(m.movie_id) as total,
  max(m.rating) as max_rating,
  min(m.rating) as min_rating,
  avg(m.rating) as average_rating,    
  max(m.votes) as max_votes,
  min(m.votes) as min_votes,
  avg(m.votes) as average_votes,  
  max(m.minutes) as max_minutes,
  min(m.minutes) as min_minutes,
  avg(m.minutes) as average_minutes  
from movies as m
join has_genre as hg on hg.movie_id = m.movie_id
join genres as g on g.genre_id = hg.genre_id
group by year, type, genre; 

--- project onto the genre and type dimentions
create view type_genre_facts as 
select
  type,              
  genre,  
  sum(f.total) as total,
  max(f.max_rating) as max_rating,
  min(f.min_rating) as min_rating,
  sum(f.total * f.average_rating)/sum(f.total) as average_rating,    
  max(f.max_votes) as max_votes,
  min(f.min_votes) as min_votes,
  sum(f.total * f.average_votes)/sum(f.total) as average_votes,      
  max(f.max_minutes) as max_minutes,
  min(f.min_minutes) as min_minutes,
  sum(f.total * f.average_minutes)/sum(f.total) as average_minutes
from all_facts as f
group by type, genre; 

--- project onto the year and type dimentions
create view type_year_facts as 
select
  type,              
  year,  
  sum(f.total) as total,
  max(f.max_rating) as max_rating,
  min(f.min_rating) as min_rating,
  sum(f.total * f.average_rating)/sum(f.total) as average_rating,    
  max(f.max_votes) as max_votes,
  min(f.min_votes) as min_votes,
  sum(f.total * f.average_votes)/sum(f.total) as average_votes,      
  max(f.max_minutes) as max_minutes,
  min(f.min_minutes) as min_minutes,
  sum(f.total * f.average_minutes)/sum(f.total) as average_minutes
from all_facts as f
group by type, year; 


--- project onto the year and genre dimentions
create view year_genre_facts as 
select
  year,
  genre, 
  sum(f.total) as total,
  max(f.max_rating) as max_rating,
  min(f.min_rating) as min_rating,
  sum(f.total * f.average_rating)/sum(f.total) as average_rating,    
  max(f.max_votes) as max_votes,
  min(f.min_votes) as min_votes,
  sum(f.total * f.average_votes)/sum(f.total) as average_votes,      
  max(f.max_minutes) as max_minutes,
  min(f.min_minutes) as min_minutes,
  sum(f.total * f.average_minutes)/sum(f.total) as average_minutes
from all_facts as f
group by year, genre; 

--- project onto the type dimention
create view type_facts as 
select
  type,              
  sum(f.total) as total,
  max(f.max_rating) as max_rating,
  min(f.min_rating) as min_rating,
  sum(f.total * f.average_rating)/sum(f.total) as average_rating,    
  max(f.max_votes) as max_votes,
  min(f.min_votes) as min_votes,
  sum(f.total * f.average_votes)/sum(f.total) as average_votes,      
  max(f.max_minutes) as max_minutes,
  min(f.min_minutes) as min_minutes,
  sum(f.total * f.average_minutes)/sum(f.total) as average_minutes
from all_facts as f
group by type; 


--- project onto the genre dimention
create view genre_facts as 
select         
  genre,  
  sum(f.total) as total,
  max(f.max_rating) as max_rating,
  min(f.min_rating) as min_rating,
  sum(f.total * f.average_rating)/sum(f.total) as average_rating,    
  max(f.max_votes) as max_votes,
  min(f.min_votes) as min_votes,
  sum(f.total * f.average_votes)/sum(f.total) as average_votes,      
  max(f.max_minutes) as max_minutes,
  min(f.min_minutes) as min_minutes,
  sum(f.total * f.average_minutes)/sum(f.total) as average_minutes
from all_facts as f
group by genre; 

--- project onto the year dimentions
create view year_facts as 
select
  year,              
  sum(f.total) as total,
  max(f.max_rating) as max_rating,
  min(f.min_rating) as min_rating,
  sum(f.total * f.average_rating)/sum(f.total) as average_rating,    
  max(f.max_votes) as max_votes,
  min(f.min_votes) as min_votes,
  sum(f.total * f.average_votes)/sum(f.total) as average_votes,      
  max(f.max_minutes) as max_minutes,
  min(f.min_minutes) as min_minutes,
  sum(f.total * f.average_minutes)/sum(f.total) as average_minutes
from all_facts as f
group by year; 

--- project onto 0 dimentions 
create view facts as 
select
  sum(f.total) as total,
  max(f.max_rating) as max_rating,
  min(f.min_rating) as min_rating,
  sum(f.total * f.average_rating)/sum(f.total) as average_rating,    
  max(f.max_votes) as max_votes,
  min(f.min_votes) as min_votes,
  sum(f.total * f.average_votes)/sum(f.total) as average_votes,      
  max(f.max_minutes) as max_minutes,
  min(f.min_minutes) as min_minutes,
  sum(f.total * f.average_minutes)/sum(f.total) as average_minutes
from all_facts as f; 



