MySQL) Instargram (2) : 인스타그램 데이터 분석하기

데이터 분석하기

 

-- 유저 중에서 가장 오래된 회원 다섯명을 찾으세요

select *
from users
order by created_at asc
limit 5;

 

-- 회원가입을 가장 많이 하는 요일은 무슨요일??

select dayname(created_at) as dayname, count(id) as count
from users
group by dayname
order by count desc;

 

-- 회원가입은 했지만, 사진은 한번도 올리지 않은 유령회원들의 데이터를 가져오시오

select *
from users u
left join photos p
on u.id = p.user_id
where p.image_url is null ;

-- 가장 유명한 사진은 무엇인지 찾아서 그 사진의 유저이름, 이미지주소, 좋아요 수를 나타내세요

select u.username, p.image_url , count(l.photo_id) as like_count
from photos p
join likes l
on p.id = l.photo_id
join users u
on p.user_id = u.id
group by l.photo_id
order by like_count desc
limit 1;

 

-- 가장 많이 사용된 해시태그의 이름은 무엇이며, 몇개나 사용되었는지 나타내시오
-- 즉 가장 많이 사용된 해시태그의 이름, 갯수를 표시하시오

select t.tag_name, count(pt.tag_id) as tag_count
from photo_tags pt
join tags t
on pt.tag_id = t.id
group by tag_id
order by tag_count desc
limit 1;