show tables;
select created_at, course_title, payment_method, email from orders;
select created_at, course_title, payment_method, email from orders;
select * from orders
where course_title != "웹개발 종합반";
select * from orders
where created_at between "2020-07-13" and "2020-07-15";
select * from checkins
where week in (1, 3);
select * from users
where email like '%daum.net';
select name, count(*) from users
group by name;
select * from users;
select * from users
where name = "신**";
select name, count(*) from users
group by name;
select week, count(*) from checkins
group by week;
select week, min(likes) from checkins
group by week;
select week, max(likes) from checkins
group by week;
select week, avg(likes) from checkins
group by week;
select week, sum(likes) from checkins
group by week;
select name, count(*) from users
group by name;
select name, count(*) from users
group by name
order by count(*);
select name, count(*) from users
group by name
order by count(*) desc;
=3=
select * from point_users
left join users
on point_users.user_id = users.user_id
select * from users u
inner join point_users p
on u.user_id = p.user_id;
select co.title, count(co.title) as checkin_count from checkins ci
inner join courses co
on ci.course_id = co.course_id
group by co.title
select * from point_users p
inner join users u
on p.user_id = u.user_id
order by p.point desc
select u.name, count(u.name) as count_name from orders o
inner join users u
on o.user_id = u.user_id
where u.email like '%naver.com'
group by u.name
select c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week
=4=
select * from point_users pu
where pu.point > (select avg(pu2.point) from point_users pu2);
select * from point_users pu
where pu.point >
(select avg(pu2.point) from point_users pu2
inner join users u
on pu2.user_id = u.user_id
where u.name = "이**");
select checkin_id, course_id, user_id, likes,
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id)
from checkins c;
select checkin_id, c3.title, user_id, likes,
(select round(avg(c2.likes),1) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
inner join courses c3
on c.course_id = c3.course_id;
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
select course_id, count(*) as cnt_total from orders
group by course_id
select a.course_id, b.cnt_checkins, a.cnt_total from
(
select course_id, count(*) as cnt_total from orders
group by course_id
) a
inner join (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) b
on a.course_id = b.course_id
select a.course_id, b.cnt_checkins, a.cnt_total, (b.cnt_checkins/a.cnt_total) as ratio from
(
select course_id, count(*) as cnt_total from orders
group by course_id
) a
inner join (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) b
on a.course_id = b.course_id
select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total) as ratio
from
(
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) a
inner join
(
select course_id, count(*) as cnt_total from orders
group by course_id
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total) as ratio
from
(
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) a
inner join
(
select course_id, count(*) as cnt_total from orders
group by course_id
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
select order_no, created_at, substring(created_at,1,10) as date from orders
select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;
