In SQL you can make queries in number of ways ,though we can break complex codes into small readable and calculated parts.
In SQL you can make queries in number of ways ,though we can break complex codes into small readable and calculated parts.
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.
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.
Movies | Rating | Views |
---|---|---|
1. Ramayana: The Legend of Prince Rama (1993) | 8.5 | 15432 |
2. Rocketry: The Nambi Effect (2022) | 8.4 | 13783 |
3. 777 Charlie (2022) | 8.4 | 13129 |
4. Hanky Panky (1979) | 8.4 | 12845 |
5. Nayakan (1987) | 8.4 | 12275 |
6. Anbe Sivam (2003) | 8.4 | 11961 |
7. Jai Bhim (2021) | 8.4 | 10032 |
8. Pariyerum Perumal (2018) | 8.4 | 8954 |
9. 3 Idiots (2009) | 8.4 | 8231 |
10. Manichithrathazhu (1993) | 8.4 | 7530 |
Give us the table, where we want a list of movies with more than average number of views.
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.
WITH temporary_frame as (select avg(Views) as avg_view FROM MOVIES)
select * FROM MOVIES,
temporary_frame where MOVIES.Views > temporary_frame.avg_view
Movies | Rating | Views |
1. Ramayana: The Legend of Prince Rama (1993) | 8.5 | 15432 |
2. Rocketry: The Nambi Effect (2022) | 8.4 | 13783 |
3. 777 Charlie (2022) | 8.4 | 13129 |
4. Hanky Panky (1979) | 8.4 | 12845 |
5. Nayakan (1987) | 8.4 | 12275 |
6. Anbe Sivam (2003) | 8.4 | 11961 |
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.
Movie | City | City_Wise_Collection | Countrywide_Collection | Budget | Verdict |
---|---|---|---|---|---|
Baahubali 2 The Conclusion (2017) | Mumbai | 1251.642 | 1788.06 | 250 | All Time Blockbuster |
KGF Chapter 2 (2022) | Mumbai | 845.6 | 1208 | 100 | All Time Blockbuster |
RRR (2022) | Mumbai | 798 | 1140 | 550 | Blockbuster |
Dangal (2016) | Mumbai | 1449.21 | 2070.3 | 70 | All Time Blockbuster |
Avengers End Game (2019) | Mumbai | 13279 | 18970 | 2500 | All Time Blockbuster |
Sanju (2018) | Mumbai | 411.95 | 588.5 | 100 | All Time Blockbuster |
Tiger Zinda Hai (2017) | Mumbai | 390.6 | 558 | 210 | Blockbuster |
Baahubali 2 The Conclusion (2017) | Delhi | 357.612 | 1788.06 | 250 | All Time Blockbuster |
KGF Chapter 2 (2022) | Delhi | 241.6 | 1208 | 100 | All Time Blockbuster |
RRR (2022) | Delhi | 228 | 1140 | 550 | Blockbuster |
Dangal (2016) | Delhi | 414.06 | 2070.3 | 70 | All Time Blockbuster |
Avengers End Game (2019) | Delhi | 3794 | 18970 | 2500 | All Time Blockbuster |
Sanju (2018) | Delhi | 117.7 | 588.5 | 100 | All Time Blockbuster |
Tiger Zinda Hai (2017) | Delhi | 111.6 | 558 | 210 | Blockbuster |
Baahubali 2 The Conclusion (2017) | Chennai | 178.806 | 1788.06 | 250 | All Time Blockbuster |
KGF Chapter 2 (2022) | Chennai | 120.8 | 1208 | 100 | All Time Blockbuster |
RRR (2022) | Chennai | 114 | 1140 | 550 | Blockbuster |
Dangal (2016) | Chennai | 207.03 | 2070.3 | 70 | All Time Blockbuster |
Avengers End Game (2019) | Chennai | 1897 | 18970 | 2500 | All Time Blockbuster |
Sanju (2018) | Chennai | 58.85 | 588.5 | 100 | All Time Blockbuster |
Tiger Zinda Hai (2017) | Chennai | 55.8 | 558 | 210 | Blockbuster |
Let’s follow the given steps to study the business problem, code, and output of the table.
Give us a table with minimum and maximum collections for each film, as well as cities.
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;
CITY | MOVIE | CITY_WISE_COLLECTION | COUNTRYWIDE_COLLECTION | BUDGET | MIN_CWC | MAX_CWC |
---|---|---|---|---|---|---|
Mumbai | Dangal (2016) | 1449.21 | 2070.3 | 70 | 207.03 | 1449.21 |
Delhi | Dangal (2016) | 414.06 | 2070.3 | 70 | 207.03 | 1449.21 |
Chennai | Dangal (2016) | 207.03 | 2070.3 | 70 | 207.03 | 1449.21 |
Mumbai | Sanju (2018) | 411.95 | 588.5 | 100 | 58.85 | 411.95 |
Delhi | Sanju (2018) | 117.7 | 588.5 | 100 | 58.85 | 411.95 |
Chennai | Sanju (2018) | 58.85 | 588.5 | 100 | 58.85 | 411.95 |
Mumbai | Tiger Zinda Hai (2017) | 390.6 | 558 | 210 | 55.8 | 390.6 |
Delhi | Tiger Zinda Hai (2017) | 111.6 | 558 | 210 | 55.8 | 390.6 |
Chennai | Tiger Zinda Hai (2017) | 55.8 | 558 | 210 | 55.8 | 390.6 |
Mumbai | Baahubali 2 The Conclusion (2017) | 1251.642 | 1788.06 | 250 | 178.806 | 1251.642 |
Delhi | Baahubali 2 The Conclusion (2017) | 357.612 | 1788.06 | 250 | 178.806 | 1251.642 |
Chennai | Baahubali 2 The Conclusion (2017) | 178.806 | 1788.06 | 250 | 178.806 | 1251.642 |
Mumbai | KGF Chapter 2 (2022) | 845.6 | 1208 | 100 | 120.8 | 845.6 |
Delhi | KGF Chapter 2 (2022) | 241.6 | 1208 | 100 | 120.8 | 845.6 |
Chennai | KGF Chapter 2 (2022) | 120.8 | 1208 | 100 | 120.8 | 845.6 |
Mumbai | RRR (2022) | 798 | 1140 | 550 | 114 | 798 |
Delhi | RRR (2022) | 228 | 1140 | 550 | 114 | 798 |
Chennai | RRR (2022) | 114 | 1140 | 550 | 114 | 798 |
Mumbai | Avengers End Game (2019) | 13279 | 18970 | 2500 | 1897 | 13279 |
Delhi | Avengers End Game (2019) | 3794 | 18970 | 2500 | 1897 | 13279 |
Chennai | Avengers End Game (2019) | 1897 | 18970 | 2500 | 1897 | 13279 |
ANCOVA is an extension of ANOVA (Analysis of Variance) that combines blocks of regression analysis and ANOVA. Which makes it Analysis of Covariance.
What if we learn topics in a desirable way!! What if we learn to write Python codes from gamers data !!
Start using NotebookLM today and embark on a smarter, more efficient learning journey!
This can be a super guide for you to start and excel in your data science career.
After tourism was established as a motivator of local economies (country, state), many governments stepped up to the plate.
Sentiment analysis can determine the polarity of sentiments from given sentences. We can classify them into certain categories.
Traverse a dictionary with for loop Accessing keys and values in dictionary. Use Dict.values() and Dict.keys() to generate keys and values as iterable. Nested Dictionaries with for loop Access Nested values of Nested Dictionaries How useful was this post? Click on a star to rate it! Submit Rating
For loop is one of the most useful methods to reuse a code for repetitive execution.
These all metrics are revolving around visits and hits which we are getting on websites. Single page visits, Bounce, Cart Additions, Bounce Rate, Exit rate,
Hypothesis testing is a statistical method for determining whether or not a given hypothesis is true. A hypothesis can be any assumption based on data.
A/B tests are randomly controlled experiments. In A/B testing, you get user response on various versions of the product, and users are split within multiple versions of the product to figure out the “winner” of the version.
This article covers ‘for’ loops and how they are used with tuples. Even if the tuples are immutable, the accessibility of the tuples is similar to that of the list.
MANOVA is an update of ANOVA, where we use a minimum of two dependent variables.
You only need to understand two or three concepts if you have read the one-way ANOVA article. We use two factors instead of one in a two-way ANOVA.