You are the SQL programmer for a mental wellbeing app, which allows users to do two types of
things. The first is to participate in a Twitter-style social network. The second is to do online units
called Steps, which are interactive therapy modules designed to exercise and develop a range
of psychological skills.
How the app works
When users register into the system, a record is created in the ‘user’ table. Upon registering, a
user can follow other users to see their posts and activity. If user x (following) starts following
user y (followed), an appropriate entry is created in the ‘user_follow’ table (x following y does not
necessarily mean that y follows x). The weight attribute of this table holds an integer that reflects
the extent to which x follows y; for example, this number would increase the more x likes or
comments on posts created by y. To help connect users, each user can also select, from a list
stored in the ‘interest’ table, interests that they have (e.g. movies, music, books). For each
interest that a user selects, a record is created in the associative table ‘user_interest’.
On the therapy side of this app, users can do online modules called Steps. Different Steps take
different amounts of time to complete and users can do a Step in one sitting or return to a Step
and complete it later on. Sometimes they might not finish a Step and will instead move on to
something else. Also, they can do any given Step as many times as they would like and each
time, they can rate how it made them feel (1 being the lowest and 5 being the highest).
As soon as user x makes a start on doing Step z, an entry is created in the ‘step_taken’ table to
record the fact that x started doing z. The timestamp of this start is also automatically recorded
in the ‘when_started’ column. If the user successfully completes the Step, the datetime of
completion is recorded in the ‘when_finished’ column.