Using SQL:
I have 3 tables, “users”, “movies”, and “events”
CREATE TABLE users (user_id INTEGER PRIMARY KEY, user_name TEXT, first_name TEXT, last_name TEXT, address TEXT, city TEXT, state TEXT)CREATE TABLE movies (movie_id INTEGER PRIMARY KEY, movie_name TEXT, movie_score TEXT)CREATE TABLE events (user_id INTEGER, movie_id INTEGER, date_watched TEXT)
The primary key for the events table is the concatenation ofuser_id, movie_id, date_watched. A user could have watched one ormore movies, one or more times. A movie could be watched by one ormore users, one or more times.
Write a SQL query that would provide the answer for each of thefollowing questions:
- Prepare a list of user_names with the number of movies thatthey have watched.
- Now adjust that query to include only a list of user_names thathave watched more than 5 distinct movies.
- Using the output from question number 2, create a column usingthe rank function to order the list by # of distinct movies watchedby user_name.
Expert Answer
Answer to Using SQL: I have 3 tables, “users”, “movies”, and “events” CREATE TABLE users (user_id INTEGER PRIMARY KEY, user_name T…