1. we want to reword the users who has been around the longest, find the oldest user.
SELECT username FROM users ORDER BY created_at ASC LIMIT 5;
2. To understand when to run the ad campaign, figure out the day of the week most users register on?
SELECT sum(id), dayname(created_at) FROM users GROUP BY username;
3. To target inactive users in an email ad campaign, find the users who have never posted a photo.
SELECT username FROM users WHERE username NOT IN (select username from photos INNER JOIN users ON photos.user_id = users.id);
4. Suppose you are running a contest to find out who got the most likes on a photo. Find out who won?
SELECT COUNT(photo_id) AS onenum, user_id, username FROM likes INNER JOIN users ON likes.user_id=users.id GROUP BY user_id ORDER BY onenum DESC;
5. The investors want to know how many times does the average user post.
SELECT * FROM photos SELECT Floor(avg(om)) FROM (SELECT user_id, COUNT(image_url) AS om FROM photos GROUP BY user_id) AS image;
6. A brand wants to know which hashtag to use on a post, and find the top most used hashtags.
SELECT tag_name FROM tags INNER JOIN photo_tags ON photo_tags.tag_id = tags.id GROUP BY tag_id ORDER BY tag_id DESC LIMIT 5;
7. To find out if there are bots, find users who have liked every single photo on the site.
SELECT user_id, COUNT(photo_id) AS liked_photos FROM likes INNER JOIN photos ON likes.user_id=photos.id GROUP BY user_id;
8. To know who the celebrities are, find users who have never commented on a photo.
SELECT username, id FROM users WHERE id NOT IN (SELECT user_id FROM comments);
9. Now it’s time to find both of them together, find the users who have never commented on any photo or have commented on every photo.
SELECT username, id FROM users WHERE id NOT IN (SELECT user_id FROM comments)
UNION ALL
SELECT username, id FROM users WHERE id IN (SELECT user_id FROM comments);