Akina

DELETE t1 
FROM review_detail t1
JOIN review_detail t2 USING (title, nickname)
WHERE t1.detail_id < t2.detail_id;

INSERT INTO people (last_name, first_name, sex, birthday)
VALUES ('Иванов', 'Иван', 'm', TO_DATE('1999-04-04', 'YYYY-MM-DD'));

SELECT DISTINCT 
       client_id,
       FIRST_VALUE(massage) 
           OVER (PARTITION BY client_id 
                 ORDER BY massage IS NULL, dttm) massage
FROM test

WITH cte AS ( SELECT *, 
                     LAG(`datetime`) OVER (PARTITION BY login ORDER BY `datetime`) lag_datetime, 
                     LAG(event) OVER (PARTITION BY login ORDER BY `datetime`) lag_event 
              FROM history )
SELECT login, SUM(TIMESTAMPDIFF(MINUTE, lag_datetime, `datetime`)) duration
FROM cte
WHERE (event, lag_event) = (2,1)
GROUP BY login;

WITH cte AS (
    SELECT 'Bob' AS name, 25 AS age UNION ALL
    SELECT 'Mark'       , 30        UNION ALL
    SELECT 'Joe'        , 35
)
SELECT test.id,
       jsonb_build_object('items', jsonb_agg(jae.value_1 || jsonb_build_object('age', cte.age)))
FROM test
CROSS JOIN jsonb_array_elements(test.value->'items') AS jae (value_1)
LEFT JOIN cte ON cte.name = jae.value_1->>'name'
GROUP BY test.id

хотелось бы посмотреть на версию mysql 5.7

SELECT department.name as department_name, 
       employee.name
FROM employee 
JOIN department ON employee.department_id = department.id 
WHERE 2 > ( SELECT COUNT(*)
            FROM employee emp
            WHERE emp.department_id = employee.department_id
              AND emp.id < employee.id )

SELECT department.name as department_name, 
       subquery.name
FROM department
JOIN ( SELECT employee.*,
              @row_number := CASE WHEN @department = department_id
                                  THEN @row_number + 1
                                  ELSE 1
                                  END rownumber,
              @department := department_id
       FROM employee
       CROSS JOIN ( SELECT @department:=0, @row_number:=0 ) variables
       ORDER BY department_id, id ) subquery ON subquery.department_id = department.id
WHERE subquery.rownumber <= 2

я просто хочу чтобы добавился столбце id, который бы шел по порядку с той таблицы которая у меня уже есть.

WITH cte AS (
    SELECT PIP.purchase_id, SUM(PIP.product_count * PR.new_price) AS all_sum
    FROM product_in_purchase AS PIP
    RIGHT OUTER JOIN price_register AS PR ON PIP.product_id = PR.product_id
    GROUP BY PIP.purchase_id
)
( SELECT * FROM cte ORDER BY all_sum ASC LIMIT 1 )
UNION 
( SELECT * FROM cte ORDER BY all_sum DESC LIMIT 1 )

from
table1 , ( table2
           left join table1 h on table1.id = h.id
           left join table2 s on table2.id = s.id )

FROM table1 
CROSS JOIN table2
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.id

FROM (table1 , table2)
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.id

CREATE OR REPLACE FUNCTION _Foo2(st TIMESTAMP, fin TIMESTAMP)
RETURNS TABLE (out_key INTEGER, out_tst timestamptz, out_val FLOAT) AS $$
DECLARE  
  
BEGIN

  RETURN QUERY EXECUTE FORMAT('
  SELECT %I, %I, %I
  FROM %I 
  WHERE %I BETWEEN ''%s'' AND ''%s'';
  ', 
  'in_key', 'in_tst', 'in_val', 'in_table', 'in_tst', st, fin);

END;

$$ LANGUAGE plpgsql;

Надо подсчитать количество строк в ответе запроса вида

SELECT COUNT(*)  AS cnt
FROM (
    SELECT 1
    FROM "order" AS o
         LEFT JOIN product p ON (p.order_id = o.id)
    GROUP BY o.id
    HAVING sum(p.price) >= 10
) x

SELECT o.id, COUNT(o.id) OVER () AS cnt
FROM "order" AS o
         LEFT JOIN product p ON (p.order_id = o.id)
GROUP BY o.id
HAVING sum(p.price) >= 10

Как отработает этот запрос?
внутренний sum посчитает сумму в рамках групп бай, если ins_date равна cur_date, а потом внешний sum суммирует полученный результат в контексте секций?