Why do these new big thing databases make SQL look like some witchcraft?

Here is some proper SQL query:

SELECT DISTINCT

       r.id,  

       r.owner_id,  

       r.name,  

       COUNT(r.id) OVER (PARTITION BY r.id) AS COUNT  

  FROM repository r  

  JOIN star s ON s.repository_id = r.id  

ORDER BY 4 DESC;