How to select one random id from the child table?

Question

I am using 10.4.13-MariaDB. I have a parent table and a child table.

Parent table (tasks)

id parent_id
4    # <- Main (parent theme) Level 2
5    4 <- child `discovered check` (level 2)
6    4 <- child  `windmill`  (level 2)
7    # <- Main (parent theme) Level 3
8    7 <- child `discovered check - level 3` (level 3)
9    7 <- child  `windmill - level 3`  (level 3)
10    # <- Main (parent theme) Level 1
11    10 <- child `discovered check - winning a piece` (level 1)
12    10 <- child  `discovered check - mate in one`  (level 1)

In interface it looks like

And I have a child table (puzzles) - puzzles table - puzzles can belong only to child themes

 id      task_id
 52         5 <- belongs to `discovered check` (level 2)
 61         6 <- belongs to `windmill` (level 2)
 25         6 <- belongs to `windmill` (level 2)
 70         11 <- belongs to `discovered check - winning a piece` (level 1)
 53         12 <- belongs to `discovered check - mate in 1` (level 1)
 62         9 <- belongs to `windmill - level 3` (level 3)
 27         9 <- belongs to `windmill - level 3` - (level 3)
 72         8 <- belongs to `discovered check - level 3` - (level 3)

I want to get ONE random PUZZLE from EACH LEVEL. How to write a proper query?

Expected result:

task_id  id
  5      52 <- random puzzle from level 2
  11     70 <- random puzzle from level 1
  8      72 <- random puzzle from level 3

My fiddle: https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=7bed2a19a0f98abccbe06ba9e0ae358b

Answer 1

With ROW_NUMBER() window function:

with cte as (
  select p.*, row_number() over (partition by t.parent_id order by rand()) rn
  from puzzles p inner join tasks t  
  on t.id = p.task_id
)
select task_id, id
from cte 
where rn = 1 

See the demo.

Partition of row_number() should be by the column parent_id of tasks, so the result has 1 row for each theme.