Activity Query A parent keeps track of the activities of a child

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.

FRIENDS
NameTypeDescription
IDINTEGERThe ID of a friend. This is the primary key.
NAMESTRINGThe name of the friend
ACTIVITYSTRINGName of the activity which the friend takes part in
ACTIVITIES
NameTypeDescription
IDINTEGERID of the activity.
NAMESTRINGName 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.

Solution:

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)

AND

cnt <> ( SELECT COUNT(activity) FROM friends GROUP BY activity ORDER BY COUNT(activity) DESC LIMIT 1))

Leave a Reply

Your email address will not be published. Required fields are marked *