select co.title,
a.course_id,
a.cnt_checkins,
b.cnt_total,
round(a.cnt_checkins/b.cnt_total,3) as ratio
from
(
select course_id,
count(distinct (user_id)) as cnt_checkins
from checkins ch
group by course_id
) a
inner join
(
select course_id, count() as cnt_total
from orders o
group by course_id
) b on a.course_id = b.course_id
inner join courses co on a.course_id = co.course_id;
--with----------;
with table1 as
(
select course_id,
count(distinct (user_id)) as cnt_checkins
from checkins ch
group by course_id
),
table2 as
(
select course_id, count() as cnt_total
from orders o
group by course_id
)
select co.title,
a.course_id,
a.cnt_checkins,
b.cnt_total,
round(a.cnt_checkins/b.cnt_total,3) as ratio
from table1 a
inner join table2 b on a.course_id = b.course_id
inner join courses co on a.course_id = co.course_id;
