Aggregate heath-check data by 1h from last 24h using SQL or Java and SQL

I have following data stored in OracleDB pulled form health check service.

I need to aggregate the results in order to get following:

• Aggregated results per API per hour for last 24h.
• There will be several tests per hour per API
  • Status "pass" is only when at least 90% of tests in each hour is "pass"
  • Status "fail" is when more than 10% of tests in each hour is "fail"
  • "unavailable" means no data available at this very hour

Outcome should be following: for 1 API (pulled by name);

[
    {"time":"14-5", "result":"pass"},
    {"time":"15-5", "result":"pass"},
    {"time":"16-5", "result":"pass"},
    {"time":"17-5", "result":"pass"},
    {"time":"18-5", "result":"unavailable"},
    {"time":"19-5", "result":"unavailable"},
    {"time":"20-5", "result":"pass"},
    {"time":"21-5", "result":"pass"},
    {"time":"22-5", "result":"pass"},
    {"time":"23-5", "result":"pass"},
    {"time":"0-6", "result":"pass"},
    {"time":"1-6", "result":"fail"},
    {"time":"2-6", "result":"pass"},
    {"time":"3-6", "result":"fail"},
    {"time":"4-6", "result":"pass"},
    {"time":"5-6", "result":"pass"},
    {"time":"6-6", "result":"pass"},
    {"time":"7-6", "result":"pass"},
    {"time":"8-6", "result":"pass"},
    {"time":"9-6", "result":"pass"},
    {"time":"10-6", "result":"pass"},
    {"time":"11-6", "result":"pass"},
    {"time":"12-6", "result":"pass"},
    {"time":"13-6", "result":"pass"}
]

It is SpringBoot application, I created custom SQL that is pulling the data:

    //Query to pull latest 24 test outcomes of single API by environment and name.
    @Query(value = "SELECT A.TST_RSLT as result, A.TST_TS as testTimeStamp FROM API_STAT A " +
            "WHERE A.ENV_NAM=?1 AND A.BUCKET_NAM = ?2 AND A.TST_TS > (SYSDATE - 1) ORDER BY TST_TS", nativeQuery = true)
    List<ApiTest.ApiTestRsltTs> findLast24RsltTsByEnvironmentNameAndBucketName(String environmentName, String bucketName);

This will give me all the results for one API for last 24 hours.

The rest is done in Java:

    @GetMapping("/apis/{environmentName}/{bucketName}/status")
    public ResponseEntity<LinkedHashMap<String, String>> getLatestStatusTestResult(
            @PathVariable(value = "environmentName") String environmentName,
            @PathVariable(value = "bucketName") String bucketName) throws ResourceNotFoundException {
        List<ApiTest.ApiTestRsltTs> apiStatusSingle = apiTestService
                .findTop24ByEnvironmentNameAndBucketNameOrderByTestTimeStampDesc(environmentName, bucketName);
        if(apiStatusSingle==null) {
            throw new ResourceNotFoundException("Status not found for: " + environmentName + "/" + bucketName);
        }

        Calendar cal = Calendar.getInstance();
        cal.setTime(new Date());
        int today = cal.get(Calendar.DAY_OF_MONTH);
        int yesterday = today -1;
        int hourNow = cal.get(Calendar.HOUR_OF_DAY);
        cal.setTime(new Date(apiStatusSingle.get(apiStatusSingle.size()-1).getTestTimeStamp().getTime()));

        LinkedHashMap<String,String> last24HoursTemporary = new LinkedHashMap<>();
        LinkedHashMap<String,String> last24Hours = new LinkedHashMap<>();

        //Get test results and store it in a map.
        for (ApiTest.ApiTestRsltTs statusSingle:apiStatusSingle) {
            cal.setTime(new Date(statusSingle.getTestTimeStamp().getTime()));
            int hourOfTest = cal.get(Calendar.HOUR_OF_DAY);
            int dayOfTest = cal.get(Calendar.DAY_OF_MONTH);
            String currentHour = hourOfTest + separatorDash + dayOfTest;
            String statusSoFar = last24HoursTemporary.get(currentHour);
            last24HoursTemporary.put(currentHour, statusSoFar + statusSingle.getResult() + ",");
        }

        //Prepare a map that will hold empty results as well.
        for(int i = hourNow+1; i<hourNow+24; i++) {
            if (i < 24) {
                last24Hours.put(String.valueOf(i) + separatorDash + yesterday,"");
            } else {
                last24Hours.put(0 + separatorDash + today,"");
                last24Hours.put(String.valueOf(i-23) + separatorDash + today, "");
            }
        }

        //Merge tables.
        for (Map.Entry<String, String> lht : last24HoursTemporary.entrySet()) {
            for (Map.Entry<String, String> lh : last24Hours.entrySet()) {
                if(lht.getKey().equals(lh.getKey()))
                    last24Hours.put(lh.getKey(), lht.getValue());
            }
        }
        
        //Aggregate test results and count percentage according to following requirements:
        //single  status (values: Pass, Fail, Unavailable)
        //<10% of the tests in the most current hour period for a given environment have failed then return Pass
        //if there is no data available then return Unavailable
        //otherwise, return Fail
        for (Map.Entry<String, String> entry : last24Hours.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            String singleValue[] = value.split(",");
            int x = 0;
            int y = 0;

            for(String sv : singleValue) {
                if(sv.contains("pass")) {
                    x++;
                } else {
                    y++;
                }
            }

            if(!value.isEmpty()) {
                if(y==0) {
                    last24Hours.put(key, "pass");
                } else {
                    if(percentage(x,y)>90) {
                        last24Hours.put(key, "pass");
                    } else {
                        last24Hours.put(key, "fail");
                    }
                }
            }
        }
        return ResponseEntity.ok().body(last24Hours);
    }

    private int percentage(int x, int y) {
        return (x/y)*100;
    }

I'm having two issues with this approach:

• I think it is clunky and it should be done better. Can this be done in pure SQL?
• The second issue is on beginning of each hour, if the health check did not kick in yet, I will get "unavailable" in recent record. Is offset helpful here?