Distinctive Analytics

There are a number of ways you can make queries in SQL, though we are suggesting a few ways through which complex codes can be broken into small, readable, and calculated parts.

First Case for “WITH” clause

If a hypothetical table is given like this, where top ratings are given to Hindi movies and their number of views worldwide. Read the following table carefully, where we list movies with the top 10 ratings and views.

Business Question 1

Give us the table, where we want a list of movies with more than average number of views.

Explanation

The average number of views for all movies is 11417. Means we need a table with only those movies which are having views greater than 11417.

Code

WITH temporary_frame as (select avg(Views) as avg_view FROM MOVIES) 
select * FROM MOVIES, 
temporary_frame where MOVIES.Views > temporary_frame.avg_view

Second case with clause

Let’s take another example where we are taking hypothetical data from a film to understand another use of the ”WITH” clause. We have a city-by-city collection of top hit movies from Indian cinema, as well as national collections.

Let’s follow the given steps to study the business problem, code, and output of the table.

Business Question 2

Give us a table with minimum and maximum collections for each film, as well as cities.

Code

Create two custom views with City Wise Collection min and City Wise Collection max via using with nested “clause” statements.

WITH minimum_collection AS
  (SELECT Movie, MIN(City_Wise_Collection) AS min_cwc
   FROM movie_business
   GROUP BY Movie),

maximum_collection AS
 (SELECT Movie, MAX(City_Wise_Collection) AS max_cwc
  FROM movie_business
  GROUP BY Movie)

SELECT
  mb.City,
  mb.Movie,
  mb.City_Wise_Collection,
  mb.Countrywide_Collection,
  mb.budget,
  min.min_cwc,
  max.max_cwc

FROM movie_business mb
 JOIN minimum_collection min
 ON mb.Movie = min.Movie
 JOIN maximum_collection max
 ON mb.Movie = max.Movie;