Question: Activity Query A parent keeps track of the activities of a child and their friends in two tables: FRIENDS and ACTIVITIES. Write a query to print the names of all the activities with neither maximum, nor minimum number of participants.
Schema: There are 2 tables: FRIENDS, ACTIVITIES.
|ID||INTEGER||The ID of a friend. This is the primary key.|
|NAME||STRING||The name of the friend|
|ACTIVITY||STRING||Name of the activity which the friend takes part in|
|ID||INTEGER||ID of the activity.|
|NAME||STRING||Name of the activity.|
Sample Input 0 2013-01-01 2013-01-01 24 34.38 34.36 34.74 35.26 35.23 35.29 35.64 36.02 36.1 36.98 37.01 36.75 36.01 35.66 34.72 33.9 32.62 31.51 30.73 29.5 26.94 25.47 23.84 22.55
Explanation Given the hourly temperature data for the 24 xp = 24 x 1 = 24 hour period starting on 2013-01- 01, the task is to predict the hourly temperature data for the 24 x n = 24 x 1 = 24 hour period starting on 2013-01-02. The table below depicts sample predictions for the given temperature data that contains a total of 19 correct predictions. The accuracy of these predictions is 19/24 = 0.79 and, because the test case weight is 5, the total score for this test case is 0.79 × 5 = 3.95.
Here is the Query:
SELECT activity from
(SELECT activity, COUNT(activity) cnt FROM friends
GROUP BY activity
HAVING cnt <> ( SELECT COUNT(activity) FROM friends GROUP BY activity ORDER BY COUNT(activity) LIMIT 1)
cnt <> ( SELECT COUNT(activity) FROM friends GROUP BY activity ORDER BY COUNT(activity) DESC LIMIT 1))