imdb_create_cards.sql

imdb_create_cards.sql

-- Seasons Rating (AVG)
select "Season", avg("Rating") "Rating Average"
from episodes
where 1 = 1
[[ and {{primary_title}} ]]
[[ and "# Votes" > {{num_votes}} ]]
group by "Season"

-- Episodes Rating
select 
  case when length(cast("Season" as varchar)) = 1 
            then case when length(cast("Episode" as varchar)) = 1 
                           then concat('S0', cast("Season" as varchar), 'E0', cast("Episode" as varchar) )
                      else concat('S0', cast("Season" as varchar), 'E', cast("Episode" as varchar) )
                 end
       else case when length(cast("Episode" as varchar)) = 1 
                      then concat('S', cast("Season" as varchar), 'E0', cast("Episode" as varchar) )
                 else concat('S', cast("Season" as varchar), 'E', cast("Episode" as varchar) )
            end
  end as episode_full_name, "Rating"
from episodes
where "Season" is not Null
and "Episode" is not Null
[[ and {{primary_title}} ]]
[[ and "# Votes" > {{num_votes}} ]]
order by 1

-- Episodes Rankings
select episode_title "Episode Title", "Season", "Episode", "Rating", "# Votes", 
  "Episode Year" "Year", concat('https://www.imdb.com/title/', "Episode ID") "Link"
from episodes
where "Season" is not null
and "Episode" is not null
[[ and {{primary_title}} ]]
[[ and "# Votes" > {{num_votes}} ]]
order by "Rating" desc
                                  
-- Movies and Series
select primary_title "Title", item_type "Item Type", genres "Genres", start_year "Year", runtime_minutes "Runtime", 
  avg_rating "Rating", num_votes "# Votes", concat('https://www.imdb.com/title/', id) "Link"
from movies_series
where 1 = 1
[[ and {{item_type}} ]]
[[ and genres like concat('%', cast((select genre from genres where {{genre}} ) as varchar(20)), '%')  ]]
[[ and {{year}} ]]
[[ and {{title}} ]]
[[ and avg_rating > {{rating}} ]]
[[ and num_votes > {{num_votes}} ]]
order by avg_rating desc