select count(*) as jml
from users;


select username, bulan, jumlah
from (select users_id, month(logs.created_at) as bulan, count(*) as jumlah
      from logs
      where year(logs.created_at) = 2019
        and users_id in (select users_id
                         from (
                                  select users_id, count(*) as jml
                                  from logs
                                  group by users_id
                                  order by jml desc
                                  limit 2) sub_dua_terbanyak)
      group by users_id, month(created_at)
      order by users_id, bulan) a
         join users on a.users_id = users.id;


#gampang
(select users_id, month(created_at) as bulan, count(*) as jumlah
 from logs
 where year(created_at) = 2019
   and users_id = 89
 group by month(created_at)
 order by bulan)
UNION
(select users_id, month(created_at) as bulan, count(*) as jumlah
 from logs
 where year(created_at) = 2019
   and users_id = 80
 group by month(created_at)
 order by bulan);

#lebih efisien where in
select users_id, month(created_at) as bulan, count(*) as jumlah
from logs
where year(created_at) = 2019
  and users_id in (89, 80)
group by users_id, month(created_at)
order by users_id, bulan

#ganti user_id mjd username
select username, bulan, jumlah
from (
         select users_id, month(created_at) as bulan, count(*) as jumlah
         from logs
         where year(created_at) = 2019
           and users_id in (89, 80)
         group by users_id, month(created_at)
         order by users_id, bulan
     ) sub_query_1
         join users on sub_query_1.users_id = users.id;

#menampilkan 2 orang yang aktifitasnya paling tinggi
select users_id
from (
         select users_id, count(*) as jumlah
         from logs
         group by users_id
         order by jumlah desc
         limit 2
     ) sub_query_2;


#tampilkan log statistik 2 orang yang paling tinggi
select username, bulan, jumlah
from (
         select users_id, month(created_at) as bulan, count(*) as jumlah
         from logs
         where year(created_at) = 2019
           and users_id in (select users_id
                            from (
                                     select users_id, count(*) as jumlah
                                     from logs
                                     group by users_id
                                     order by jumlah desc
                                     limit 2
                                 ) sub_query_2)
         group by users_id, month(created_at)
         order by users_id, bulan
     ) sub_query_1
         join users on sub_query_1.users_id = users.id;