PostgreSQL - SQL function to loop through all months of the year and pull 10 random records from eac-CodePudding

I am attempting to pull 10 random records from each month of this year using this query here but I get an error "ERROR: relation "c1" does not exist " Not sure where I'm going wrong - I think it may be I'm using Mysql syntax instead, but how do I resolve this?

My desired output is like this

Month	Another header
2021-01	random email 1
2021-01	random email 2

total of ten random emails from January, then ten more for each month this year (til November of course as Dec yet to happen)..

With CTE AS 
(
    Select  month, 
            email, 
            Row_Number() Over (Partition By month Order By FLOOR(RANDOM()*(1-1000000 1))) AS RN
    From    (
            SELECT
                    DISTINCT(TO_CHAR(DATE_TRUNC('month', timestamp ), 'YYYY-MM')) AS month
                    ,CASE
                      WHEN 
                      JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (form_data,0),'name') = 'email' 
                      THEN 
                      JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (form_data,0),'value')
                      END AS email
            FROM  form_submits_y2 fs
            WHERE fs.website_id IN (791)
            AND month LIKE '2021%'
            GROUP BY 1,2
            ORDER BY 1 ASC
            )
)
SELECT  *
FROM CTE C1
LEFT JOIN 
     (SELECT RN
            ,month
            ,email
     FROM CTE C2
     WHERE C2.month = C1.month 
     ORDER BY RANDOM() LIMIT 10) C3
ON C1.RN = C3.RN
ORDER By month ASC```

CodePudding user response：

You can't reference an outer table inside a derived table with a regular join. You need to use left join lateral to make that work

CodePudding user response：

I did end up finding a more elegant solution to my query here via this source from github :

SELECT 
month
,email
FROM
(
    Select  month, 
            email, 
            Row_Number() Over (Partition By month Order By FLOOR(RANDOM()*(1-1000000 1))) AS RN
    From    (
            SELECT
                    TO_CHAR(DATE_TRUNC('month', timestamp ), 'YYYY-MM') AS month
                    ,CASE
                      WHEN JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (form_data,0),'name') = 'email' 
                      THEN JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (form_data,0),'value')
                      END AS email
            FROM  form_submits_y2 fs
            WHERE fs.website_id IN (791)
            AND month LIKE '2021%'
            GROUP BY 1,2
            ORDER BY 1 ASC
            )
) q
WHERE
  RN <=10
ORDER BY month ASC