Jois fazem parte do SQL não há como fugir, apenas tens falta de familiaridade com eles. A abordagem de escrita é sim boa, já que, ele segregou varias partes em CTE. Se fosse mal feito seria uma série de subselects dentro da query principal. Por exemplo, ao ler a query temos:
latest_published_child_contents: Uma CTE que seleciona os IDs e caminhos dos conteúdos mais recentes que foram publicados nas últimas 24 horas e têm um parent_id não nulo (ou seja, são filhos de outros conteúdos).
latest_interacted_root_contents:

• Uma CTE que combina duas seleções
  • A primeira parte seleciona os conteúdos raiz (não têm parent_id) que foram publicados nas últimas 7 dias e têm um saldo positivo de "tabcoins"
  • A segunda parte seleciona os conteúdos raiz que estão associados aos conteúdos da CTE latest_published_child_contents, garantindo que não tenham o mesmo owner_id. Isso inclui novamente o cálculo de saldo de "tabcoins"
    ranked_published_root_contents: Uma CTE que seleciona conteúdos raiz da CTE latest_interacted_root_contents que possuem um saldo positivo de "tabcoins".
    Calcula um "score" para cada conteúdo com base em uma fórmula envolvendo o saldo de "tabcoins" e o número de proprietários únicos dos conteúdos que possuem uma relação hierárquica com o conteúdo atual.
    group_1 a group_5: CTEs que dividem os conteúdos da CTE ranked_published_root_contents em grupos com base em critérios específicos de tempo e pontuação. Os grupos são definidos por intervalos de tempo e pontuações mínimas.
    Cada grupo é ordenado e limitado a um número máximo de resultados.
    ranked
    Uma CTE final que une os resultados das CTEs group_1 a group_5 e os classifica novamente com base em um critério de classificação mais amplo.
    A consulta final também inclui informações detalhadas sobre os conteúdos, como IDs, proprietários, títulos, pontuações, etc.
    Os resultados finais são ordenados por grupo, pontuação e data de publicação.

Ou seja, se tu quebrar as partes fica bem fácil de entender, e na área em que trabalho (engenharia de dados/bi) a gente acaba precisando escrever querys muito mais complexas, então creio que seja familiaridade mesmo.