Working in PostgreSQL, I intend to use the output of a CTE as a reference to join another CTE below it. I've used this style countless times in SQL Server and Synapse, however I'm finding it difficult to replicate in…
Query 1: WITH cte AS ( SELECT *, MAX(score) OVER (PARTITION BY exam_id) AS max_score, MIN(score) OVER (PARTITION BY exam_id) AS min_score FROM student JOIN exam USING (student_id) ORDER BY exam_id, student_id ) SELECT * FROM cte; By Query 1,…
I expected the function call "expensive" to be called only once, but it appears to be called for every row in "children". Can the CTE/query be tweaked to only call the function once and have that value returned for every…
I want to be able to call set_config to set a configuration parameter within the same transaction/scope as an INSERT statement. There is a function triggered AFTER the insert which reads from this configuration parameter. I need to accomplish this…
Given a directed acyclic graph shaped like this: "Branch A" "Branch B" branch-a-head-id branch-b-head-id --- | | ^ id-Z id-C | | | | id-Y id-B | | | "Branch A" id-X id-A and | | "Branch B" branch-a-first-id branch-b-first-id…
I have a following SQL running in PostgreSQL v13.10: WITH stuckable_statuses AS ( SELECT status_id FROM status_descriptions WHERE (tags @> ARRAY['stuckable']::varchar[]) ) SELECT jobs.* FROM jobs WHERE jobs.status = ANY(select status_id from stuckable_statuses) And it is running really slow while…
we want to "fill in the gaps" of the record_ids and fill in missing values with the most recent available value. If there is no value available, use the first available value instead. The result should look like: category record_id…
I'm working with AWS RDS MySQL and using MySQL Workbench to develop the queries before moving them into the Lambda integration function for my HTTP API on AWS API Gateway. I created this query: use prod_esports; WITH muuid AS (select…
I wrote the below query with a CTE attempting to return all the dates on which daily transactions exceeded the average of all daily transactions; however, MySQL keeps returning an error message for something around line 1. I've attached a…
I have the following query: WITH matching_data as ( select * from consultants_matching_data_for_project(6001) ) select id, matching_data.city_matching from consultant_profiles LEFT OUTER JOIN matching_data on matching_data.consultant_profile_id = consultant_profiles.id; Is there any other way to join with matching_data result without using a…