SELECT
FROM users u
left join point_users pu on u.user_id = pu.user_id ;
SELECT
FROM users u
inner join point_users pu on u.user_id = pu.user_id ;
SELECT
FROM orders o
inner join users u on o.user_id = u.user_id ;
SELECT c.course_id ,
c2.title ,
COUNT(c.likes) as cnt
FROM checkins c
inner join courses c2 on c.course_id = c2.course_id
GROUP BY c.course_id ;
SELECT pu.user_id ,
u.name ,
u.email ,
pu.point
FROM point_users pu
inner join users u on pu.user_id= u.user_id
ORDER BY pu.point DESC ;
SELECT u.name ,
COUNT() as cnt
FROM orders o
inner join users u on o.user_id = u.user_id
WHERE o.email LIKE '%naver.com'
GROUP by u.name ;
SELECT u.name ,
COUNT() as cnt_name
FROM enrolleds e
inner join users u on e.user_id = u.user_id
WHERE e.is_registered =0
GROUP by u.name
ORDER by cnt_name DESC ;
SELECT c.course_id ,
c.title ,
COUNT() as cnt_notstart
FROM courses c
inner join enrolleds e on c.course_id =e.course_id
WHERE e.is_registered =0
GROUP by c.title ;
SELECT co.title ,
ch.week,
COUNT() as cnt
FROM courses co
inner join checkins ch on co.course_id = ch.course_id
GROUP by co.title ,ch.week
ORDER by co.title ,ch.week;
SELECT co.title ,
ch.week,
COUNT() as cnt
FROM courses co
inner join checkins ch on co.course_id = ch.course_id
GROUP by co.title ,ch.week
ORDER by co.title ASC ,ch.week DESC ;
SELECT co.title ,
ch.week,
COUNT() as cnt
FROM courses co
inner join checkins ch on co.course_id = ch.course_id
INNER JOIN orders o on ch.user_id = o.user_id
WHERE o.created_at >= '2020-08-01'
GROUP by co.title , ch.week
ORDER BY co.title , ch.week;
SELECT u.name,
COUNT() as cnt
FROM users u
left join point_users pu on u.user_id = pu.user_id
WHERE pu.point_user_id IS NULL
GROUP by u.name ;
SELECT u.name,
COUNT() as cnt
FROM users u
left join point_users pu on u.user_id = pu.user_id
WHERE pu.point_user_id IS NOT NULL
GROUP by u.name ;
SELECT COUNT(pu.point_user_id) as pnt_user_cnt,
COUNT(u.user_id ) as tot_user_cnt,
ROUND( COUNT(pu.point_user_id) / COUNT(u.user_id ),2) as ratio
FROM users u
left join point_users pu on u.user_id = pu.user_id
WHERE u.created_at
BETWEEN '2020-07-10' AND '2020-07-20';
(select '7월' as month,
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
)
union all
(select '8월' as month,
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
);
SELECT e.enrolled_id,
e.user_id ,
COUNT() as max_count
FROM enrolleds e
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
WHERE ed.done = 1
GROUP by e.enrolled_id, e.user_id
ORDER BY COUNT(*) DESC
limit 0, 10;
