The database pictured above is a very primitive example of what an Instagram clone could potentially be. Showing basic data points to showchase queries and table relationships. Created and obtained from a Udemy course.

• Getting five oldest users.

  SELECT 
      username, 
      created_at,
      CONCAT(TIMESTAMPDIFF(DAY, created_at, NOW()), ' days old') AS age
  FROM users
  ORDER BY created_at
  LIMIT 5;

  | username | created_at | age | |------------------|---------------------|---------------| | Darby_Herzog | 2016-05-06 00:14:21 | 2492 days old | | Emilio_Bernier52 | 2016-05-06 13:04:30 | 2492 days old | | Elenor88 | 2016-05-08 01:30:41 | 2490 days old | | Nicole71 | 2016-05-09 17:30:22 | 2488 days old | | Jordyn.Jacobson2 | 2016-05-14 07:56:26 | 2484 days old |
  
  Determining which day of the week is most popular. Can also extract day of week in name with DAYNAME(created_at) instead.

  SELECT 
      DATE_FORMAT(DATE(created_at), '%W') AS day_of_week,
      COUNT(*) AS total
  FROM users
  GROUP BY DATE_FORMAT(DATE(created_at), '%W')
  ORDER BY total DESC; 
  

  | day_of_week | total | |-------------|-------| | Thursday | 16 | | Sunday | 16 | | Friday | 15 | | Tuesday | 14 | | Monday | 14 | | Wednesday | 13 | | Saturday | 12 |
  
  
  Find inactive users, defined by zero pictures posted. Alternatively, query where photo.id is null.

  SELECT 
      username,
      image_url 
  FROM users
  LEFT JOIN photos 
      ON users.id = photos.user_id
  WHERE image_url IS NULL;
  

  | username | image_url | |---------------------|-----------| | Aniya_Hackett | NULL | | Bartholome.Bernhard | NULL | | Bethany20 | NULL | | Darby_Herzog | NULL | | David.Osinski47 | NULL | | Duane60 | NULL | | Esmeralda.Mraz57 | NULL | | Esther.Zulauf61 | NULL | | Franco_Keebler64 | NULL | | Hulda.Macejkovic | NULL | | Jaclyn81 | NULL | | Janelle.Nikolaus81 | NULL | | Jessyca_West | NULL | | Julien_Schmidt | NULL | | Kasandra_Homenick | NULL | | Leslie67 | NULL | | Linnea59 | NULL | | Maxwell.Halvorson | NULL | | Mckenna17 | NULL | | Mike.Auer39 | NULL | | Morgan.Kassulke | NULL | | Nia_Haag | NULL | | Ollie_Ledner37 | NULL | | Pearl7 | NULL | | Rocio33 | NULL | | Tierra.Trantow | NULL |
  
  
  
  Pictures/posts with the most likes.

  SELECT
      image_url,
      u.username,
      COUNT(*) AS total_likes
  FROM photos
  JOIN users u 
      ON u.id = photos.user_id
  JOIN likes 
      ON likes.photo_id = photos.id
  GROUP BY photos.id
  ORDER BY total_likes DESC
  LIMIT 5; 
   

  | image_url | username | total_likes | |------------------------|-----------------|-------------| | jarret.name | Zack_Kemmer93 | 48 | | celestine.name | Malinda_Streich | 43 | | dorcas.biz | Adelle96 | 43 | | shannon.org | Seth46 | 42 | | dejon.name | Delpha.Kihn | 41 |
  
  
  How many times does an aveage user post? There were multiple ways to approach this e.g. is considered an 'inactive' user -- 0 posts? 0 likes? In this instance, simplicity is best. Thus, the query takes into account for all users and all posts.

  SELECT (SELECT COUNT(*) FROM photos) / (SELECT COUNT(*) FROM users) AS avg_post;
  

  | avg_post | |----------| | 2.5700 |
  
  
  Find the top five most common hashtags. An example application of this information is to use these tags in order to increase the chances of your picture to trend through the explore page. However, this data did include ties so the top 5 can be extended to top 8.

  SELECT
      tags.tag_name,
      COUNT(*) AS tag_count
  FROM photo_tags
  JOIN tags 
      ON photo_tags.tag_id = tags.id
  GROUP BY tags.id
  ORDER BY tag_count DESC
  LIMIT 5;

  | tag_name | tag_count | |----------|-----------| | smile | 59 | | beach | 42 | | party | 39 | | fun | 38 | | concert | 24 |
  
  
  Since social media is riddled with bots, this query finds which users are bots that likes all user posts. There are two approaches shown below.

  SELECT * 
  FROM (
      SELECT 
          user_id,
          username,
          CASE
              WHEN COUNT(*) = (SELECT COUNT(*) FROM photos) THEN 1
              ELSE 0
          END as bot_status
          FROM likes
          JOIN users ON users.id = likes.user_id
          GROUP BY user_id) AS temp
  WHERE temp.bot_status = 1;

  | user_id | username | bot_status | |---------|--------------------|------------| | 5 | Aniya_Hackett | 1 | | 91 | Bethany20 | 1 | | 54 | Duane60 | 1 | | 14 | Jaclyn81 | 1 | | 76 | Janelle.Nikolaus81 | 1 | | 57 | Julien_Schmidt | 1 | | 75 | Leslie67 | 1 | | 24 | Maxwell.Halvorson | 1 | | 41 | Mckenna17 | 1 | | 66 | Mike.Auer39 | 1 | | 71 | Nia_Haag | 1 | | 36 | Ollie_Ledner37 | 1 | | 21 | Rocio33 | 1 |

  SELECT
  	username, 
  	COUNT(*) AS num_likes 
  FROM users 
  INNER JOIN likes 
  	ON users.id = likes.user_id 
  GROUP BY likes.user_id 
  HAVING num_likes = (SELECT Count(*) FROM photos); 

  | username | num_likes | |--------------------|-----------| | Aniya_Hackett | 257 | | Bethany20 | 257 | | Duane60 | 257 | | Jaclyn81 | 257 | | Janelle.Nikolaus81 | 257 | | Julien_Schmidt | 257 | | Leslie67 | 257 | | Maxwell.Halvorson | 257 | | Mckenna17 | 257 | | Mike.Auer39 | 257 | | Nia_Haag | 257 | | Ollie_Ledner37 | 257 | | Rocio33 | 257 |