You can use the following HQL (SQL) query to find the sum of new cases for each continent and identify the country with the highest number of cases in each continent along with the corresponding case count: WITH ContinentSum AS (
SELECT
continent,
SUM(new_cases) AS total_new_cases
FROM
sample_table_test
GROUP BY
continent
),
CountryMaxCases AS (
SELECT
continent,
location AS country,
MAX(total_case) AS max_cases
FROM
sample_table_test
GROUP BY
continent, location
)
SELECT
cs.continent,
cs.total_new_cases,
cm.country,
cm.max_cases
FROM
ContinentSum cs
JOIN
CountryMaxCases cm
ON
cs.continent = cm.continent
AND cs.total_new_cases = cm.max_cases; This query first calculates the sum of new cases for each continent in the ContinentSum CTE (Common Table Expression). Then, it finds the country with the highest total cases in each continent using the CountryMaxCases CTE. Finally, it joins the results from both CTEs to provide the desired output. Sample resultset for the shared data. +---------------+---------------------+--------------+---------------+
| cs.continent | cs.total_new_cases | cm.country | cm.max_cases |
+---------------+---------------------+--------------+---------------+
| Asia | 25.0 | Afghanistan | 25.0 |
+---------------+---------------------+--------------+---------------+
... View more
