Analysis of the Medan City District Court, Indonesia

The analysis of the Medan District Court is a project aimed at analyzing the data of the Case Tracking Information System of the Medan District Court, Indonesia. The goal of this project is to gain insights from the data of the Medan District Court on how they operate.

The questions I aimed to answer in this project are as follows:

1. Find the top 3 cases entered
2. Average that cases entered at 2017 – 2022
3. find out the performance of the medan District Court in resolving cases by year
4. find what cases that often incomplete by year
5. what happened to the medan district court during covid-19
6. Are there any unique case patterns in a given year?

To tackle these questions, I employed four tools: Excel, MySQL, and Power Bi . Here's a brief overview of how I made this project :

Data preparation

• Translate data to English language
• Delete column archive and not archive
• Add new column to help analyzing the data

1. "Success Indicator" column: This column is used to categorize cases based on the following conditions:

        - If the cases completed >= cases entered, then it falls into category 1.
        - If the cases completed < cases entered, then it falls into category 2.
        - If the cases entered = 0, then it falls into category 3.
        - Category 1 means successfully resolving cases that were entered.
        - Category 2 means there is a remainder of cases entered.
        - Category 3 means there are no cases entered.
   

2. "Number of Completed Cases" column: This column is used to determine the number of cases that have been completed by subtracting the remaining cases from the remaining cases of the previous month.

how I used each tool for the project:

• excel : i translate the data in excel using find and replace feature. And then delete column archieve and not archive. After all setup, I moved my data to mysql
• mysql : Performing various data manipulations that refer to the aforementioned question targets. After all ready, I export it to be csv file
• power bi : The data from MySQL is imported into Power BI for data visualization purposes.

1. Find the top 3 cases entered

From 2017 to 2022, the graph below illustrates the top 3 cases with the highest number of entries.

2. Average that cases entered from 2017 to 2022

Notes : The calculation excludes cases with zero entries.

The graph indicates a declining trend from 2020 to 2022, possibly attributed to the impact of the COVID-19 pandemic during those years, which restricted the mobility of the Medan community.

3. Find out the performance of the medan District Court in resolving cases by year

2017

2018

2019

2020

2021

2022

• From the above graph, it is evident that the Medan District Court performs well in resolving ordinary criminal law cases, with an achievement consistently above 40% each year.
• Successful resolution of traffic cases was notable only in 2018, despite traffic cases dominating 85% of the average cases since 2017 to 2022.
• The resolution of petition cases reached its peak performance in 2020.

4. Find what cases that often incomplete from 2017 to 2022

• The Industrial Relations Court leads with 35 unresolved cases, followed by petitions with 32 instances.
• Corruption cases rank among the top 3 unresolved cases.

5. What happened to the medan district court during covid-19?

• The traffic cases still dominate 85% of the entries. This implies that the Medan community, even during the pandemic lockdown, remains active outside their homes, posing a potential risk of accidents.
• There has been a 4% increase in ordinary criminal law cases compared to the previous year

6. Are there any unique case patterns in a given year?

• From 2017 to 2021, the Medan District Court has predominantly resolved cases related to ordinary criminal law, despite the highest number of incoming cases being related to traffic offenses.
• In 2017, ordinary criminal law cases constituted the highest percentage, reaching 43.88% of the total incoming cases for that year. However, in the subsequent years, there was a drastic decline in ordinary law cases. There is a possibility that since 2018, the Medan city government has been rigorously enforcing the law

here's the sql code :

/*create table*/
create table cases (
month int, 
year int, 
number int,
classification varchar (100), 
remaining_cases_flm int,
cases_entered int, 
cases_completed int,
remaining_cases int);

select * from cases;

/*add column as succes indicator*/
SELECT month, year, classification, cases_entered, cases_completed,
    CASE 
        WHEN cases_entered = 0 THEN 3
        WHEN cases_completed >= cases_entered THEN 1
        WHEN cases_completed < cases_entered THEN 2
    END AS success_indicator
FROM cases;

alter table cases add column success_indicator int;

update cases 
set success_indicator = (
 CASE  WHEN cases_entered = 0 THEN 3
        WHEN cases_completed >= cases_entered THEN 1
        WHEN cases_completed < cases_entered THEN 2
    END
);

/*add column sum of done cases*/
alter table cases add column sum_of_cases_done int;

update cases 
set sum_of_cases_done = (remaining_cases - remaining_cases_flm);

/*top cases that come in by year*/
select year, classification, sum(cases_entered) as sum_of_cases
from cases 
where cases_entered <> 0 and cases_completed <> 0
group by year, classification
order by year asc;

/*average cases that come in by year*/
select year, CEILING (avg(cases_entered)) as average_cases
from cases
where success_indicator <> 3
group by year ;

/*cases that often incomplete */
select year, classification, count(classification) as count_of_incomplete_cases
from cases 
where success_indicator = 2 
group by year, classification 
order by year asc, count_of_incomplete_cases desc ;

/* performance of medan court*/

select month, year, classification, remaining_cases - remaining_cases_flm as sum_of_cases_done

from cases 

where success_indicator <> 3

group by year, classification 

order by year asc, month asc ;

/*cases where the Medan district court often performs well*/

select month, year, classification, sum_of_cases_done

from cases 

where success_indicator <> 3 and sum_of_cases_done <=0

group by year, classification

order by year asc, month asc ;

/*cases where the Medan district court often has poor performance*/

select month, year, classification, sum_of_cases_done

from cases 

where success_indicator <> 3 and sum_of_cases_done >0

group by year, classification

order by year asc, month asc ;