<commit_before><NAME>

1. Mencari team yang bermain pertama
-> select country_name from soccer_country where country_id in (
   select team_id from match_details where match_no = 1);

2. Mencari juara Euro 2016
-> select country_name 
   from soccer_country
   where country_id in (

   select team_id 
   from match_details
   where play_stage = 'F' and win_lose = 'W');

3. Mencari pertandingan dengan penonton terbanyak
-> select match_no, play_stage, goal_score, audence 
   from match_mast
   where audence in (

   select max(audence) 
   from match_mast); 

4. Mencar match nomor pertandingan germany vs poland
-> select match_no 
   from match_details
   where team_id in (
   
   select country_id 
   from soccer_country 
   where country_name = 'Germany') 
   and match_no in (
   
   select match_no 
   from match_details
   where team_id in (
   
   select country_id 
   from soccer_country 
   where country_name = 'Poland'))

5. Menampilkan match_no, play_date, play_stage, goal_score, and results dari pertandingan portugal vs hungary
-> select match_no, play_stage, play_date, goal_score, results
   from match_mast
   where match_no in (

   select match_no 
   from match_details
   where team_id in (

   select country_id 
   from soccer_country 
   where country_name = 'Portugal') 
   and match_no in (
   
   select match_no 
   from match_details
   where team_id in (
   
   select country_id 
   from soccer_country 
   where country_name = 'Hungary')))
 
7. Menampilkan team yang memiliki penonton terbanyak saat bertanding
-> select country_name 
   from soccer_country 
   where country_id in (

   select team_id 
   from goal_details
   where match_no in(

   select match_no 
   from match_mast
   where audence in(

   select max(audence) 
   from match_mast))); 
  
8. Menampilkan pencetak goal terakhir portugal
-> select player_name 
   from player_mast
   where player_id in (

   select player_id 
   from goal_details 
   where goal_time in (

    select max(goal_time) 
    from goal_details
    where match_no in(

     select match_no 
     from match_details
     where match_no in (

      select max(match_no) from match_details
      where team_id in (

       select country_id 
       from soccer_country
       where country_name = 'Portugal')))));

9.   <commit_msg>menambah tugas subquery versi 2<commit_after><NAME>

1. Mencari team yang bermain pertama
-> select country_name from soccer_country where country_id in (
   select team_id from match_details where match_no = 1);

2. Mencari juara Euro 2016
-> select country_name 
   from soccer_country
   where country_id in (

   select team_id 
   from match_details
   where play_stage = 'F' and win_lose = 'W');

3. Mencari pertandingan dengan penonton terbanyak
-> select match_no, play_stage, goal_score, audence 
   from match_mast
   where audence in (

   select max(audence) 
   from match_mast); 

4. Mencar match nomor pertandingan germany vs poland
-> select match_no 
   from match_details
   where team_id in (
   
   select country_id 
   from soccer_country 
   where country_name = 'Germany') 
   and match_no in (
   
   select match_no 
   from match_details
   where team_id in (
   
   select country_id 
   from soccer_country 
   where country_name = 'Poland'))

5. Menampilkan match_no, play_date, play_stage, goal_score, and results dari pertandingan portugal vs hungary
-> select match_no, play_stage, play_date, goal_score, results
   from match_mast
   where match_no in (

   select match_no 
   from match_details
   where team_id in (

   select country_id 
   from soccer_country 
   where country_name = 'Portugal') 
   and match_no in (
   
   select match_no 
   from match_details
   where team_id in (
   
   select country_id 
   from soccer_country 
   where country_name = 'Hungary')));

6.
 
7. Menampilkan team yang memiliki penonton terbanyak saat bertanding
-> select country_name 
   from soccer_country 
   where country_id in (

   select team_id 
   from goal_details
   where match_no in(

   select match_no 
   from match_mast
   where audence in(

   select max(audence) 
   from match_mast))); 
  
8. Menampilkan pencetak goal terakhir portugal
-> select player_name 
   from player_mast
   where player_id in (

   select player_id 
   from goal_details 
   where goal_time in (

    select max(goal_time) 
    from goal_details
    where match_no in(

     select match_no 
     from match_details
     where match_no in (

      select max(match_no) from match_details
      where team_id in (

       select country_id 
       from soccer_country
       where country_name = 'Portugal')))));

9. Menampilkan tambahan waktu terbanyak kedua di Euro 2016
-> select stop2_sec 
   from match_mast
   where stop2_sec in (

    select max(stop2_sec)
    from match_mast
    where stop2_sec < 

    (select max(stop2_sec)
    from match_mast));   

10. Menampilkan nama team yg bertanding dengan tambahan waktu terbanyak kedua
    select country_name 
    from soccer_country
    where country_id in (

     select team_id
     from match_details
     where match_no in (

  select match_no
  from match_mast 
  where stop2_sec in (

   select stop2_sec 
   from match_mast
   where stop2_sec in (

    select max(stop2_sec)
    from match_mast
    where stop2_sec < 

  (select max(stop2_sec)
   from match_mast)))));

11. Menampilkan match_no, play_date dan tambahan waktu tertinggi kedua
-> select match_no, play_date, stop2_sec 
   from match_mast
   where stop2_sec in (

   select stop2_sec 
   from match_mast
   where stop2_sec in (

   select max(stop2_sec)
   from match_mast
   where stop2_sec < 

   (select max(stop2_sec)
   from match_mast)));

12. Menampilkan team yg kalah di final
->  select country_name 
    from soccer_country
    where country_id in (

    select team_id
    from match_details
    where play_stage = 'F' and win_lose = 'L');

13.

14. Pencetak gol penalty tercepat
-> select player_id, jersey_no
from player_mast
where player_id in (

select player_id 
from goal_details
where goal_id in (

select goal_id 
from goal_details 
where match_no in (

select min(match_no) 
from goal_details
where match_no in (

select match_no
from goal_details
where goal_type = 'P'))) and goal_type = 'P'); 
 
15. Pencetak gol penalty tercepat beserta negaranya
-> select player_id,team_id, jersey_no
from player_mast
where player_id in (

select player_id 
from goal_details
where goal_id in (

select goal_id 
from goal_details 
where match_no in (

select min(match_no) 
from goal_details
where match_no in (

select match_no
from goal_details
where goal_type = 'P'))) and goal_type = 'P');

16. Penjaga gawang italy saat menahan penalty lawan jerman
-> select player_name 
from player_mast
where player_id in (

 select player_gk
 from penalty_gk
 where match_no in (

  select match_no
  from penalty_gk
  where team_id in(

   select country_id
   from soccer_country
   where country_name = 'Germany')) 

   and match_no in (

   select match_no
   from penalty_gk
   where team_id in(

    select country_id
    from soccer_country
    where country_name = 'Italy'))) and team_id in(
    
    select country_id
    from soccer_country
    where country_name = 'Italy'); 

17. Jumlah goal jerman
-> select count(team_id)
from goal_details
where team_id in (

select country_id
from soccer_country
where country_name = 'Germany');

18. Penjaga gawang inggris
-> select player_name, jersey_no
from player_mast
where posi_to_play = 'GK' and team_id in(

select country_id
from soccer_country
where country_name = 'England');

19. Pemain inggris dari liverpool
select *
from player_mast
where playing_club = 'Liverpool' and team_id in(

select country_id
from soccer_country
where country_name = 'England');

20. Pemain pencetak goal terakhir di semifinal kedua
-> select *
from player_mast
where player_id in (

select player_id
from goal_details
where match_no = 50 and goal_time in (

select max(goal_time) 
from goal_details
where match_no = 50));

21. Kapten Portugal
-> select player_name 
from player_mast
where player_id in (

select player_captain
from match_captain
where team_id in (

select country_id
from soccer_country
where country_name = 'Portugal')); 

22. jumlah pemain perancis yg bermain di final
-> select count(player_id)+11 as jumlah
from player_in_out
where in_out = 'I' and match_no in (

select match_no 
from match_mast 
where play_stage = 'F') and team_id in (

select country_id
from soccer_country
where country_name = 'France');

23. Kiper jerman yang ngga kebobolan di fase grup
-> select player_name 
from player_mast
where player_id in (

select player_gk
from match_details
where play_stage = 'G' and team_id in (

select country_id
from soccer_country
where country_name = 'Germany'));

24. Runner up Euro 2016
-> select country_name 
from soccer_country
where country_id in(

select team_id 
from match_details
where play_stage = 'F' and win_lose = 'L'); 

25. jumlah penalty terbanyak berdasarkan negara
-> select count(kick_id)
from penalty_shootout
group by team_id
order by count(kick_id) desc limit 1;

26. jumlah penalty terbanyak berdasarkan pemain
-> select count(kick_id)
from penalty_shootout
group by player_id
order by count(kick_id) desc limit 1;

27. jumlah penalty terbanyak berdasarkan pemain
-> select count(kick_id)
from penalty_shootout
group by match_no
order by count(kick_id) desc limit 1;

28. Negara bertanding dengan penalty terbanyak
-> select country_name
from soccer_country
where country_id in(

select team_id 
from penalty_shootout
where match_no in(

select match_no
from penalty_shootout
group by match_no
order by count(kick_id) desc limit 1));

29. Penendang penalty ke tujuh dari portugal
-> select player_id 
from penalty_shootout
where kick_no = '7' and team_id in (

select country_id
from soccer_country
where country_name = 'Portugal');

30. play stage pada penalty ke 23 di tendang
select play_stage 
from match_mast
where match_no in (

select distinct match_no
from penalty_shootout
where kick_id = '23');

31. venue tempat berlangsungnya adu penalti
-> select venue_name
from soccer_venue
where venue_id in (

select venue_id 
from match_mast
where match_no in (

select distinct match_no
from penalty_shootout)); 

32. Tanggal berlangsungnya adu penalti
-> select play_date 
from match_mast
where match_no in (

select distinct match_no
from penalty_shootout); 

33. goal tercepat setelah menit ke 5
-> select *
from goal_details
where goal_time in (

select min(goal_time)
from goal_details
where goal_time in(

select goal_time
from goal_details
where goal_time > 5));
{'fraction_non_alphanumeric': 0.050032916392363395, 'fraction_numerical': 0.01119157340355497, 'mean_word_length': 3.617455492835432, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5387012', 'n_tokens_mistral': 3755, 'n_tokens_neox': 3505, 'n_words': 1188}