GROUP BY and GROUP_CONCAT for calculating competitions ranking

Question

I would like some help with a problem that has had stumped me for two days.

I have 'results' table like this:

result_id	competition_id	competitor_id	competitor_ranking
1	1	1	0.1
2	1	2	0.4
3	1	3	0.2
4	1	4	0.3
5	2	1	0.4
6	2	2	0.1
7	2	3	0.2
8	2	5	0.3
9	3	3	0.1
10	3	4	0.4
11	3	5	0.2
12	3	6	0.3

From the 'results' table, I want to get a grouped ranking of competitors with penalties points (+1.0) included, like this:

competitor_id	competitions	rankings	ranking_with_penalties
1	1; 2; M	0.1; 0.4	0.1; 0.4; +1.0
2	1; 2; M	0.4; 0.1	0.4; 0.1; +1.0
3	1; 2; 3	0.2; 0.2; 0.1	0.2; 0.2; 0.1
4	1; M; 3	0.3; 0.4	0.3; +1.0; 0.4
5	M; 2; 3	0.3; 0.2	+1.0; 0.3; 0.2
6	3; M; M;	0.3	0.3; +1.0; +1.0

I know that group_concat function is an aggregate function that concatenates all non-null values in a column. I understand that the task is quite trivial. But I can not solve it.

CREATE TABLE results (
  result_id INTEGER PRIMARY KEY, 
  competition_id INTEGER,
  competitor_id INTEGER,
  competitor_ranking 
);

INSERT INTO results(competition_id, competitor_id, competitor_ranking) VALUES 
  (1, 1, 0.1), (1, 2, 0.4), (1, 3, 0.2), (1, 4, 0.3),
  (2, 1, 0.4), (2, 2, 0.1), (2, 3, 0.2), (2, 5, 0.3),
  (3, 3, 0.1), (3, 4, 0.4), (3, 5, 0.2), (3, 6, 0.3)
;

SELECT
  competitor_id,
  group_concat(coalesce(competition_id, NULL), '; ') AS competitions,
  group_concat(coalesce(competitor_ranking, NULL), '; ') AS rankings,
  group_concat(coalesce(NULLIF(competitor_ranking, NULL), '+1.0'), '; ') AS ranking_with_penalties
FROM results
GROUP BY competitor_id;

• M's are symbols means that a competitor missed a competition and follows the ranking_with_penalties logic. I need to get the missed competitions in the grouped sample of competitors and replace them with penalty points (+1.0) for calculating final ranking. min one competition a competitor should visit.
  • added the above from the comments

I'm looking forward to any help.

Answer 1

I believe the following produces the result you want

• i.e.
  • note competitor 6 being in competition 3 is not according to your expected result (but should probably be as the result above i.e. M (comp 1); M (comp 2); 3 (comp 3) as per the previous results)
  • likewise for the rankings (position(s) of +1.0)

Achieved using :-

WITH 
    cte_comp_competitor_matrix AS (
        SELECT DISTINCT results.competition_id,c2.competitor_id FROM results JOIN results AS c2 ON 1
    ),
    cte_stage2 AS (
        SELECT competitor_id,competition_id,
             CASE cccm.competitor_id IN(SELECT competitor_id FROM results WHERE competition_id = cccm.competition_id) 
                WHEN 1 THEN competition_id ELSE 'M' END
            AS matched
        FROM cte_comp_competitor_matrix AS cccm ORDER BY competitor_id
    ),
    cte_stage3 AS (
        SELECT *,
            coalesce(
                (
                    SELECT competitor_ranking 
                    FROM results 
                    WHERE cte_stage2.competitor_id = results.competitor_id 
                        AND cte_stage2.competition_id = results.competition_id
                ),
                '+1.0'
            ) AS competitor_ranking
        FROM cte_stage2
    )
SELECT 
    competitor_id,
    group_concat(matched,';') AS competitions,
    group_concat(competitor_ranking,';') AS rankings
FROM cte_stage3
GROUP BY competitor_id
;

• cte_comp_competitor_matrix retrieves every competitor/competition combintation

  • 

• cte_stage2 (for want of a better name) applies the M (missing) `

• cte_stage3 (better name??) applies the rankings (and thus effectively the original + extra rows for the missed competitions)

  • 

• CTE = Common Table Expression which is a temporary table that lasts for the duration of the execution (and as can be seen your can have multiple CTE's). They have been used to progressively reach the desired result.

  • they have purposefully not been reduced to shorter code to hopefully make them easier to understand

Answer 2

Your problem is that you don't have a competitor or competition table. You need to start with cross-joining those, then left-join your main table.

You can fake it with a CTE if need be, but you should really create those tables.

WITH competition AS (
    SELECT DISTINCT
      r.competition_id
    FROM results r
),
competitor AS (
    SELECT DISTINCT
      r.competitor_id
    FROM results r
)
SELECT
  cr.competitor_id,
  GROUP_CONCAT(IFNULL(r.competition_id, 'M'), '; ') AS competitions,
  GROUP_CONCAT(r.competitor_ranking, '; ') AS rankings,
  GROUP_CONCAT(IFNULL(r.competitor_ranking, '+1.0'), '; ') AS ranking_with_penalties
FROM competitor cr
CROSS JOIN competition cn
LEFT JOIN results r
   ON r.competition_id = cn.competition_id
  AND r.competitor_id = cr.competitor_id
GROUP BY
  cr.competitor_id;

db<>fiddle

In more modern versions of SQLite you can use ORDER BY within the GROUP_CONCAT to make it a deterministic ordering. For example

GROUP_CONCAT(IFNULL(r.competition_id, 'M'), '; ' ORDER BY cn.competition_id)