PostgreSQL Query not returning the proper results

So this is my table structure

learning_paths
id
name
version
created_at
updated_at

learning_path_levels
id
name
learning_path_id
order
created_at
updated_at

learning_path_level_nodes
id
name
description
documentation_links
evaluation_methodology
learning_path_level_id
created_at
updated_at

learning_path_node_users
id
learning_path_level_node_id
user_id
evaluated_by
evaluated_at
is_successful
created_at
updated_at

I'm writing a query to retrieve the learning_path_name, count of the amount of levels each learning path has, the pending and completed nodes per level for the user, and the total amount of nodes per level.

I have the following query

select learning_paths."name",
sum(case when learning_path_node_users.is_successful and learning_path_node_users.user_id is not null then 1 else 0 end) as completed_nodes,
sum(case when learning_path_node_users.is_successful = false or learning_path_node_users.user_id is null then 1 else 0 end) as pending_nodes,
count(learning_path_levels.id) as total_levels,
count(*) as total_nodes
from learning_path_level_nodes
inner join learning_path_levels on learning_path_levels.id = learning_path_level_nodes.learning_path_level_id
inner join learning_paths on learning_paths.id = learning_path_levels.learning_path_id
left join learning_path_node_users on learning_path_node_users.learning_path_level_node_id = learning_path_level_nodes.id
group by learning_paths."name"

which returns:

name	completed_nodes	pending_nodes	total_levels	total_nodes
Devops	5	3	8	8
QA	0	1	1	1
Project manager	3	3	6	6
AI	0	5	5	5

Everything is correct, except for the levels count, for example, for Devops,it should be 2, and it is returning 8 for Project Manager it should be 2, and it is returning 6 a pattern I see is that it returns the amount of nodes as the amount of levels,

How can I fix this? I'd really appreciate any help or suggestions, as I've been struggling with this. Thanks in advance

EDIT: As per your suggestion, I'm attaching a fiddle with the tables and data.

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f29676ff7051686a28de96928db1e3a6

Solution

While I don't get the exact results you want, I think you want to add a distinct to your count for the total levels:

select
  lp.name,
  sum(case when u.is_successful and u.user_id is not null then 1 else 0 end) as completed_nodes,
  sum(case when u.is_successful = false or u.user_id is null then 1 else 0 end) as pending_nodes,
  count(distinct lpl.id) as total_levels, -- added "distinct"
  array_agg (lpl.id) as level_detail, -- debugging aid
  count(*) as total_nodes
from
  learning_path_level_nodes n
  join learning_path_levels lpl on lpl.id = n.learning_path_level_id
  join learning_paths lp on lp.id = lpl.learning_path_id
  left join learning_path_node_users u on u.learning_path_level_node_id = n.id
group by
  lp.name

To help expose the rationale, I added the field level_detail, which you can delete, to show why the results are what they are. You can obviously remove that once the results are what you want.

If it's not what you expect, perhaps you can explain or give by example what I might be missing.