[Udemy] Working With Lots of Instagram Data

kaitlin_k·2022년 1월 17일
0

SQL

목록 보기
18/21
post-thumbnail

The Ultimate MySQL Bootcamp 15강을 공부하며 정리한 내용입니다.

15강에서는 방대한 양의 데이터를 가지고 회사에서 실제로 동료로부터 받을 수 있는 요청을 sql문으로 작성해 데이터를 조회하는 연습을 해보도록 한다.

🙋💬

"we want to reward our users who have been around the longest. Find the 5 oldest users."

SELECT * 
FROM users
ORDER BY created_at ASC
LIMIT 5;

🙋💬

"What day of the week do most users register on? We need to figure out when to schedule an ad campaign"

SELECT
    DAYNAME(created_at) AS dayname,
    COUNT(*) AS total
FROM users
GROUP BY dayname
ORDER BY total DESC
LIMIT 1;

🙋💬

"We want to target our inactive users with an email campaign. Find the users who have never posted a photo"

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

🙋💬

"We're running a new contest to see who can get the most likes on a single photo. Who won?"

SELECT
  username,
  photos.id, 
  count(*) AS total
FROM photos 
INNER JOIN likes 
  ON photos.id = likes.photo_id
INNER JOIN users
  ON users.id = photos.user_id
GROUP BY photos.id
ORDER BY total DESC
LIMIT 1;        

🙋💬

"Our investors want to know... How many times does the average user post?"

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

🙋💬

"A brand wants to know which hashtags to use in a post. What are the top 5 most commonly used hashtags?"

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

🙋💬

"We have a small problem with bots on our site... Find users who have liked every single photo on the site"

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);

새로 배운 내용

group byhaving

  • having 절은 where 절과 비슷하지만, 그룹을 나타내는 결과 집합의 행에만 적용된다는 점에서 차이가 있다.
  • having 절은 group by 뒤에 작성해주어야한다.
  • 반면, where 절은 개별 행에 적용된다.
  • where 절은 group by 앞에 사용해야하며, 집계함수와는 함께 사용할 수 없다.
  • 쿼리에는 where 절과 having 절이 모두 포함될 수 있다.

예를 들어, 다음 문장을 아래와 같이 쿼리문으로 작성할 수 있다.

"titles와 publishers 테이블을 조인하여 캘리포니아 주에 있는 출판사들 중 출판 도서의 평균가격을 구하고, 이때 평균가격이 10달러 이상인 경우로 제한한다."

SELECT 
  titles.pub_id, 
  AVG(titles.price)
FROM titles 
INNER JOIN publishers
  ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(price) > 10;

reference

profile
어제보다 나은 오늘을 만드는 중

0개의 댓글