hope you are doing well

I need to calculate the total number of rows (children) that a package has

input data

StartDate  EndDate    PlanSystemId  Package_Id  Package_Type    month   mode    rnumber 
2024-03-19 2024-04-14 93167253       null        Package       2024-04  null      1          
2024-03-19 2024-04-14 93170470       93167253    Child         2024-04  MENSUEL   2 
2024-03-19 2024-04-14 93170471       93167253    Child         2024-04  MENSUEL   3 
2024-03-19 2024-04-14 93167253       null        Package       2024-03  null      1          
2024-03-19 2024-04-14 93170470       93167253    Child         2024-03  MENSUEL   2 

• In the month 2024-04 the package (plansystemid 93167253) has 2 childs
• In the month 2024-03 the package (plansystemid 93167253) has 1 child

output data

PlanSystemId    Package_Id  Package_Type    month    mode   rnumber number_childs
93167253         null        Package       2024-04   null         1          2
93167253         null        Package       2024-03   null         1          1

I have this query, maybe you can help to improve it. If you have a better option please let me know.
Thanks in advance

WITH CTE_parent_child AS (
SELECT 
  Startdate, EndDate,PlanSystemId,Placement_code,
  Package_Id,Package_Type,month,mode,
  ,ROW_NUMBER() OVER (PARTITION BY month, IFNULL(Package_Id,PlanSystemId ) ORDER BY PlanSystemId ) AS rnumber

FROM table
WHERE (package_type = 'Package' or package_type='Child') 
  AND (PlanSystemId = 93167253  or Package_Id = 93167253) 
)
SELECT 
  Startdate,EndDate, PlanSystemId, Package_Id,
  Package_Type,month, mode,rnumber,
  MAX(rnumber) over(partition by month, Package_Id ORDER BY PlanSystemId ) as number_childs
  ,COUNT(CASE WHEN Package_Type = 'Child' THEN 1 END) OVER (PARTITION BY month, Package_Id) AS number_childs

FROM CTE_parent_child
ORDER BY month,plansystemID,IFNULL(Package_Id,PlanSystemId ) desc;