Are there standards for writing complex nested SQL queries?

Question

PHP

To recalculate statistics, you have to write complex queries with a lot of nesting, like:

UPDATE staff
SET rating = (
  (
    SELECT SUM(value * weight) AS a
    FROM `ratings`
    WHERE entity_type = 'DOCTOR'
          AND entity_id = staff.url
          AND entity_city = (SELECT city
                             FROM clinics
                             WHERE clinics.account_id = staff.account_id
                            )
          AND published = 1
  )
  /
  (
    SELECT SUM(weight) AS b
    FROM `ratings`
    WHERE entity_type = 'DOCTOR'
          AND entity_id = staff.url
          AND entity_city = (SELECT city
                             FROM clinics
                             WHERE clinics.account_id = staff.account_id
                            )
          AND published = 1
  )
),
  ratingCommentCount = (
    SELECT COUNT(*) AS count
    FROM `ratings`
    WHERE entity_type = 'DOCTOR'
          AND entity_id = staff.url
          AND entity_city = (SELECT city
                             FROM clinics WHEREclinics.account_id = staff.account_id
                            )
AND published = 1
);

Are there any rules for writing multi-level READABLE sql code?
When writing through the Query Builder, readability becomes even worse, and writing is sometimes more difficult if the logic is more complicated than a simple selection.
Execute in separate queries - an order of magnitude slower on a large table (250.000).
It is not necessary to advise you to optimize the query or denormalize above, this is just an example.
In PHP I tried to write something like this:

$aSumSql = 'SELECT SUM(value * weight)'; 
$bSumSql = 'SELECT SUM(weight)'; 
DB::update("UPDATE staff SET rating = ($aSumSql) / ($bSumSql), ...............");

But it is not clear whether it is more beautiful or even worse. =)
Written 2 minutes ago

Answer 1

there are no standards.
There is a rule - multi-line code must be uniform and formatted.
In such cases, I use strings to store the query and raw query to execute it.

Answer 2

Display all amounts in one query, and do an UPDATE with the second.

Answer 3

There are no standards. But you can agree on a conditional standard within your team. For example, that all commands will start from a new line, service words will be in upper case, etc.