SQL Programmer for a Mental Well Being App

The SQL Tasks
In this section are listed 10 questions for you to answer. Write one (single) SQL statement per
question. Subqueries and nesting are allowed within a single SQL statement – however, you will
be penalized for writing overly complicated SQL statements. DO NOT USE VIEWS to answer
questions.
1. Find all the ongoing/unfinished steps. Display the title of these steps and full names of
the users who are taking these steps. (1 mark)
2. List the themes and the number of the steps associated with these themes. Display the
theme name and number of associated steps sorted in descending order. (1 mark)
3. Which step is the least popular based on the average rating given by users? Display the
title and ID of the step and its average rating (formatted to 2 decimal places). Only include
those steps which are rated by at least one user. (1 mark)
4. Find the steps that are taken the greatest number of times. Display the ID, title and count
of the times the step has been taken. In case of ties, display all the steps with the same
number of times taken. (2 marks)
5. Who is the most followed user between age of 15 and 18? Display the age (as an integer),
first name, and last name of such user along with the number of followers. (2 marks)
Hint: The function TIMESTAMPDIFF can be used to subtract two timestamps. The
function CURDATE returns current date.
6. Find all steps that are never taken or are taken exactly once? Display the id and title of
these steps along with the indication how many times the step has been taken (0 or 1).
(2 marks)
7. Find users who started taking step ‘Doing and being’ after they had started the step ‘Panic’
but have never completed ‘Panic’. Display the user ID, first name and last name.
(2 marks)
8. What finished steps were completed both by a user with first name “Alice” and a user
with first name “Bob”? Display the ID and title of such steps along with the number of
times each user has completed these steps. (3 marks)
9. Find the top two users with the highest number of interests. For those two users, find out
the common steps taken by both of them. Display the titles of the common steps they
have taken and the number of times those steps are taken by each user. (3 marks)
10. For each user taking a step, calculate how many other users have taken the same step.
We are only interested in the cases where the step is performed by at least 5 other users.
Display the user ID, number of other users (at least 5 other users) who are taking the
same step and the title of the taken step. (3 marks)

 

Loader Loading...
EAD Logo Taking too long?

Reload Reload document
| Open Open in new tab

Download [231.63 KB]