It seems like you're trying to write a SQL query to select the `hacker_id` and `name` from the `hackers` table and the sum of the maximum `score` from the `submissions` table for each hacker. However, there are a few syntax errors in your query. Here's a corrected version:

```sql
SELECT
h.hacker_id,
h.name,
SUM(s.max_score)
FROM
hackers h
JOIN
(SELECT
hacker_id,
MAX(score) as max_score
FROM
submissions
GROUP BY
hacker_id) s
ON
h.hacker_id = s.hacker_id
GROUP BY
h.hacker_id, h.name
HAVING
SUM(s.max_score) 0
ORDER BY
SUM(s.max_score) DESC, h.hacker_id;
```

Here's a step-by-step explanation:

1. The subquery `(SELECT hacker_id, MAX(score) as max_score FROM submissions GROUP BY hacker_id)` is used to get the maximum score for each hacker from the `submissions` table.

2. This subquery is then joined with the `hackers` table on `hacker_id`.

3. The `SELECT` statement then selects the `hacker_id` and `name` from the `hackers` table, and the sum of the maximum scores from the subquery.

4. The `GROUP BY` clause groups the results by `hacker_id` and `name`.

5. The `HAVING` clause filters out the hackers whose sum of maximum scores is not greater than 0.

6. Finally, the `ORDER BY` clause sorts the results in descending order by the sum of maximum scores, and then by `hacker_id` in case of ties.

Question

It seems like you're trying to write a SQL query to select the `hacker_id` and `name` from the `hackers` table and the sum of the maximum `score` from the `submissions` table for each hacker. However, there are a few syntax errors in your query. Here's a corrected version:

```sql
SELECT 
    h.hacker_id,
    h.name,
    SUM(s.max_score)
FROM 
    hackers h 
JOIN 
    (SELECT 
        hacker_id, 
        MAX(score) as max_score 
     FROM 
        submissions 
     GROUP BY 
        hacker_id) s 
ON 
    h.hacker_id = s.hacker_id
GROUP BY 
    h.hacker_id, h.name
HAVING 
    SUM(s.max_score) > 0 
ORDER BY 
    SUM(s.max_score) DESC, h.hacker_id;
```

Here's a step-by-step explanation:

1. The subquery `(SELECT hacker_id, MAX(score) as max_score FROM submissions GROUP BY hacker_id)` is used to get the maximum score for each hacker from the `submissions` table.

2. This subquery is then joined with the `hackers` table on `hacker_id`.

3. The `SELECT` statement then selects the `hacker_id` and `name` from the `hackers` table, and the sum of the maximum scores from the subquery.

4. The `GROUP BY` clause groups the results by `hacker_id` and `name`.

5. The `HAVING` clause filters out the hackers whose sum of maximum scores is not greater than 0.

6. Finally, the `ORDER BY` clause sorts the results in descending order by the sum of maximum scores, and then by `hacker_id` in case of ties.

Knowee AI · Accepted Answer

It seems like you're trying to write a SQL query to select the `hacker_id` and `name` from the `hackers` table and the sum of the maximum `score` from the `submissions` table for each hacker. However, there are a few syntax errors in your query. Here's a corrected version:

```sql
SELECT 
    h.hacker_id,
    h.name,
    SUM(s.max_score)
FROM 
    hackers h 
JOIN 
    (SELECT 
        hacker_id, 
        MAX(score) as max_score 
     FROM 
        submissions 
     GROUP BY 
        hacker_id) s 
ON 
    h.hacker_id = s.hacker_id
GROUP BY 
    h.hacker_id, h.name
HAVING 
    SUM(s.max_score) > 0 
ORDER BY 
    SUM(s.max_score) DESC, h.hacker_id;
```

Here's a step-by-step explanation:

1. The subquery `(SELECT hacker_id, MAX(score) as max_score FROM submissions GROUP BY hacker_id)` is used to get the maximum score for each hacker from the `submissions` table.

2. This subquery is then joined with the `hackers` table on `hacker_id`.

3. The `SELECT` statement then selects the `hacker_id` and `name` from the `hackers` table, and the sum of the maximum scores from the subquery.

4. The `GROUP BY` clause groups the results by `hacker_id` and `name`.

5. The `HAVING` clause filters out the hackers whose sum of maximum scores is not greater than 0.

6. Finally, the `ORDER BY` clause sorts the results in descending order by the sum of maximum scores, and then by `hacker_id` in case of ties.

select h.hacker_id,h.name,sum(max(s.score)) from hackers h JOIN submissions s ON h.hacker_id=s.hacker_idgroup by h.hacker_idhaving sum(max(s.score)>0 order by sum(max(s.score) desc,h.hacker_id;

Question

Solution

Similar Questions

Upgrade your grade with Knowee