How to write complex queries in SQL with nested selects in a declarative style?

Question

MySQL

I'm not asking you to solve a specific problem, but I want to learn the principle. There is a base (mysql) with very large both horizontally and vertically tables. It is necessary to generate various types of report tables, preferably using only SQL queries in order to speed up the work.
What is the difficulty:
In the generated tables, some cells must contain calculated values, for example, one cell must be like this: from another table, get a list of records with a specific `name`, find the value of `name` with the second most `value`, the other cell must contain the name of the column from another table, the third - the value obtained from another database at all, the fifth - the sum of the value with a specific name. In general, cell values ​​can be calculated in different ways.
How I do it now using imperative programming:
Python wrapper loops through a huge list of names (which is received by SELECT) and uses each value as a parameter for a stored procedure, which, in turn, creates for each cell which you need to calculate a temporary table, all of them are then inserted into the result table, that is, something like this happens:

CREATE TEMPORARY TABLE `tmp1` ENGINE=MEMORY AS (SELECT * FROM `input` WHERE `name`="xxx")
...

INSERT INTO `result`
SELECT
    tmp1.id AS id,
    count(tmp2.names) AS count_names,
    max(tmp3.values) AS max_value,
    tmp4.second_max AS second_max,
    ...
FROM tmp1, tmp2, tmp3, tmp4

I assume that this scheme is not optimal, and I want to use only pure SQL, I tried to use LOOP, but for some reason the performance dropped dozens of times, and imperative programming in SQL, in my opinion, is hell.
How do you even write such complex queries in a declarative style that process a bunch of rows at once? Guru SQL, share.

Answer 1

You can write 1 big query using sub queries

select t1.some_field,

   (select t2.value
    from otherTable t2 
    where  t2.name = t1.name
    order by t2.value
    limit 2, 1) as second_value,

   (select sum(t2.value)
    from otherTable t2 
    where  t2.name = t1.name) as total_value

from table_1 t1