just cheked,
really there are identical category name but different id.
It's look very strange as it is expected that category name should be unique as well as id.

Did you check whether they have identical category_id on those "4 most viewed post"?

Solution with JOIN LATERAL is not working correctly (it returns for some categories 4 most viewed posts intead of 2). At the same time solution with ROW_NUMBER returns right result. Please try two solutions and you will see...

The magic part is you can reference the source table on the right side of the LATERAL Subquery.
Same result as rank over window function. Great kata!

The JOIN type is the key.

This is a kata what I have learned that the important part is

1. crosstab takes three attributes/column, where the first is the identifier of the pivot table, second is the categories, third will be the values.
2. the query in the crosstab should be text/char.

Can someone please tell me if the email is empty input the xpath function will return NULL as place holder or no in the array?
Everything is fine but only the order of the email address I can't solve it.