Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

I want to group adgroups by adgroupid in the below query SELECT reports.date AS date,…

  1. User

    I want to group adgroups by adgroupid in the below query

    SELECT reports.date AS date, reports.trafficchannelid AS trafficchannelid, reports.final AS final, reports.compressedreport AS compressedreport, JSON_AGG( JSON_BUILD_OBJECT( 'campaignid', campaignid, 'funneltemplateid', funneltemplateid, 'campaignidrt', campaignidrt, 'adGroups', ( SELECT JSON_AGG( JSON_BUILD_OBJECT( 'adgroupid', report_adgroups.adgroupid, 'ad', ( SELECT JSON_BUILD_OBJECT( 'adid', report_ads.adid, 'adname', report_ads.adname, 'adgroupname', report_ads.adgroupname, 'campaignname', report_ads.campaignname, 'date', report_ads.date, 'total_spent', report_ads.total_spent, 'total_click', report_ads.total_click, 'total_impressions', report_ads.total_impressions, 'revenue', report_ads.revenue, 'estimate', report_ads.estimate, 'feedsource', report_ads.feedsource, 'clickdetail', ( SELECT JSON_AGG( JSON_BUILD_OBJECT( 'track_id', report_ads_clickdetail.track_id, 'domain_id', report_ads_clickdetail.domain_id ) ) FROM report_ads_clickdetail WHERE report_ads_clickdetail.report_ads_id = report_ads.id and report_ads_clickdetail.report_id = reports.id GROUP BY report_adgroups.adgroupid ) ) FROM report_ads WHERE report_ads.report_adgroups_id = report_adgroups.id ) ) ) FROM report_adgroups WHERE report_adgroups.report_details_id = report_details.id GROUP BY report_details.campaignid,report_details.funneltemplateid,report_details.campaignidrt ) ) ) AS details FROM reports LEFT JOIN report_details ON report_details.report_id = reports.id LEFT JOIN report_adgroups ON report_adgroups.report_details_id = report_details.id LEFT JOIN report_ads ON report_ads.report_adgroups_id = report_adgroups.id LEFT JOIN report_ads_clickdetail ON report_ads_clickdetail.report_ads_id = report_ads.id WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21' GROUP BY reports.trafficchannelid, reports.final, reports.compressedreport, reports.date

  2. Assistant

    To group adgroups by adgroupid in the query, you can modify the subquery that fetches the adgroups:

    SELECT 
        reports.date AS date,
        reports.trafficchannelid AS trafficchannelid,
        reports.final AS final,
        reports.compressedreport AS compressedreport,
        JSON_AGG(
            JSON_BUILD_OBJECT(
               'campaignid', campaignid,
               'funneltemplateid', funneltemplateid,
               'campaignidrt', campaignidrt,
               'adGroups', (
                    SELECT JSON_AGG(
                        JSON_BUILD_OBJECT(
                           'adgroupid', report_adgroups.adgroupid,
                           'ads', (
                                SELECT JSON_AGG(
                                    JSON_BUILD_OBJECT(
                                       'adid', report_ads.adid,
                                       'adname', report_ads.adname,
                                       'adgroupname', report_ads.adgroupname,
                                       'campaignname', report_ads.campaignname,
                                       'date', report_ads.date,
                                       'total_spent', report_ads.total_spent,
                                       'total_click', report_ads.total_click,
                                       'total_impressions', report_ads.total_impressions,
                                       'revenue', report_ads.revenue,
                                       'estimate', report_ads.estimate,
                                       'feedsource', report_ads.feedsource,
                                       'clickdetail', (
                                            SELECT JSON_AGG(
                                                JSON_BUILD_OBJECT(
                                                   'track_id', report_ads_clickdetail.track_id,
                                                   'domain_id', report_ads_clickdetail.domain_id
                                                )
                                            )
                                            FROM report_ads_clickdetail
                                            WHERE report_ads_clickdetail.report_ads_id = report_ads.id and report_ads_clickdetail.report_id = reports.id
                                            GROUP BY report_ads.adid
                                        )
                                    )
                                    FROM report_ads
                                    WHERE report_ads.report_adgroups_id = report_adgroups.id
                                )
                            )
                        )
                    )
                    FROM report_adgroups
                    WHERE report_adgroups.report_details_id = report_details.id
                    GROUP BY report_adgroups.adgroupid
                )
            )
        ) AS details
    FROM reports
    LEFT JOIN report_details ON report_details.report_id = reports.id 
    LEFT JOIN report_adgroups ON report_adgroups.report_details_id = report_details.id 
    LEFT JOIN report_ads ON report_ads.report_adgroups_id = report_adgroups.id
    LEFT JOIN report_ads_clickdetail ON report_ads_clickdetail.report_ads_id = report_ads.id
    WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21'
    GROUP BY reports.trafficchannelid, reports.final, reports.compressedreport, reports.date
    

    In this modified query, I have changed the grouping of the adGroups subquery to GROUP BY report_adgroups.adgroupid. This will group the adgroups by their adgroupid.

  3. User

    The problem I am facing with the query is grouping issue. A row is repeated numerous times and is not grouped by. Can you please add group by option in the necessary areas and places?

  4. Assistant

    Apologies for the confusion. Here's an updated version of the query with the necessary groupings:

    SELECT 
        reports.date AS date,
        reports.trafficchannelid AS trafficchannelid,
        reports.final AS final,
        reports.compressedreport AS compressedreport,
        JSON_AGG(
            JSON_BUILD_OBJECT(
               'campaignid', campaignid,
               'funneltemplateid', funneltemplateid,
               'campaignidrt', campaignidrt,
               'adGroups', (
                    SELECT JSON_AGG(
                        JSON_BUILD_OBJECT(
                           'adgroupid', report_adgroups.adgroupid,
                           'ads', (
                                SELECT JSON_AGG(
                                    JSON_BUILD_OBJECT(
                                       'adid', report_ads.adid,
                                       'adname', report_ads.adname,
                                       'adgroupname', report_ads.adgroupname,
                                       'campaignname', report_ads.campaignname,
                                       'date', report_ads.date,
                                       'total_spent', report_ads.total_spent,
                                       'total_click', report_ads.total_click,
                                       'total_impressions', report_ads.total_impressions,
                                       'revenue', report_ads.revenue,
                                       'estimate', report_ads.estimate,
                                       'feedsource', report_ads.feedsource,
                                       'clickdetail', (
                                            SELECT JSON_AGG(
                                                JSON_BUILD_OBJECT(
                                                   'track_id', report_ads_clickdetail.track_id,
                                                   'domain_id', report_ads_clickdetail.domain_id
                                                )
                                            )
                                            FROM report_ads_clickdetail
                                            WHERE report_ads_clickdetail.report_ads_id = report_ads.id and report_ads_clickdetail.report_id = reports.id
                                        )
                                    )
                                    FROM report_ads
                                    WHERE report_ads.report_adgroups_id = report_adgroups.id
                                    GROUP BY report_ads.adid  -- Group by adid
                                )
                            )
                        )
                        FROM report_adgroups
                        WHERE report_adgroups.report_details_id = report_details.id
                        GROUP BY report_adgroups.adgroupid  -- Group by adgroupid
                    )
                )
            )
        ) AS details
    FROM reports
    LEFT JOIN report_details ON report_details.report_id = reports.id 
    LEFT JOIN report_adgroups ON report_adgroups.report_details_id = report_details.id 
    LEFT JOIN report_ads ON report_ads.report_adgroups_id = report_adgroups.id
    LEFT JOIN report_ads_clickdetail ON report_ads_clickdetail.report_ads_id = report_ads.id
    WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21'
    GROUP BY reports.date, reports.trafficchannelid, reports.final, reports.compressedreport 
    

    In this updated query, I have added the GROUP BY clauses for report_ads.adid to group the ads within each ad group and report_adgroups.adgroupid to group the ad groups within each campaign. Additionally, I have also added the reports.date to the GROUP BY clause to ensure proper grouping.

  5. User

    How to choose only the last insertion on the table reports? I have a created_at and updated_at field

  6. Assistant

    To select only the last insertion on the reports table based on the created_at or updated_at fields, you can use the ORDER BY clause with the corresponding field in descending order and limit the result to one row. Here's an example:

    SELECT 
        reports.date AS date,
        reports.trafficchannelid AS trafficchannelid,
        reports.final AS final,
        reports.compressedreport AS compressedreport,
        JSON_AGG(
            JSON_BUILD_OBJECT(
               'campaignid', campaignid,
               'funneltemplateid', funneltemplateid,
               'campaignidrt', campaignidrt,
               'adGroups', (
                    SELECT JSON_AGG(
                        JSON_BUILD_OBJECT(
                           'adgroupid', report_adgroups.adgroupid,
                           'ads', (
                                SELECT JSON_AGG(
                                    JSON_BUILD_OBJECT(
                                       'adid', report_ads.adid,
                                       'adname', report_ads.adname,
                                       'adgroupname', report_ads.adgroupname,
                                       'campaignname', report_ads.campaignname,
                                       'date', report_ads.date,
                                       'total_spent', report_ads.total_spent,
                                       'total_click', report_ads.total_click,
                                       'total_impressions', report_ads.total_impressions,
                                       'revenue', report_ads.revenue,
                                       'estimate', report_ads.estimate,
                                       'feedsource', report_ads.feedsource,
                                       'clickdetail', (
                                            SELECT JSON_AGG(
                                                JSON_BUILD_OBJECT(
                                                   'track_id', report_ads_clickdetail.track_id,
                                                   'domain_id', report_ads_clickdetail.domain_id
                                                )
                                            )
                                            FROM report_ads_clickdetail
                                            WHERE report_ads_clickdetail.report_ads_id = report_ads.id and report_ads_clickdetail.report_id = reports.id
                                        )
                                    )
                                    FROM report_ads
                                    WHERE report_ads.report_adgroups_id = report_adgroups.id
                                    GROUP BY report_ads.adid
                                    ORDER BY report_ads.created_at DESC  -- or use report_ads.updated_at if preferred
                                    LIMIT 1
                                )
                            )
                        )
                        FROM report_adgroups
                        WHERE report_adgroups.report_details_id = report_details.id
                        GROUP BY report_adgroups.adgroupid
                    )
                )
            )
        ) AS details
    FROM reports
    LEFT JOIN report_details ON report_details.report_id = reports.id 
    LEFT JOIN report_adgroups ON report_adgroups.report_details_id = report_details.id 
    LEFT JOIN report_ads ON report_ads.report_adgroups_id = report_adgroups.id
    LEFT JOIN report_ads_clickdetail ON report_ads_clickdetail.report_ads_id = report_ads.id
    WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21'
    GROUP BY reports.date, reports.trafficchannelid, reports.final, reports.compressedreport
    ORDER BY reports.created_at DESC  -- or use reports.updated_at if preferred
    LIMIT 1
    

    In this updated query, I added the ORDER BY reports.created_at DESC (or reports.updated_at DESC) clause at the end to sort the rows in descending order based on the created_at (or updated_at) field. Then, I included the LIMIT 1 clause to only retrieve the last row based on the sorting. This will ensure that you get only the latest insertion in the reports table.

  7. User

    It says it is not part of the group by. How can I use it without adding in the group by?

  8. Assistant

    Apologies for the confusion. If you want to select the last insertion from the reports table without including the reports.created_at (or reports.updated_at) field in the GROUP BY clause, you can use a subquery to filter the results based on the latest created_at (or updated_at) value. Here's an updated version of the query:

    SELECT 
        reports.date AS date,
        reports.trafficchannelid AS trafficchannelid,
        reports.final AS final,
        reports.compressedreport AS compressedreport,
        JSON_AGG(
            JSON_BUILD_OBJECT(
               'campaignid', campaignid,
               'funneltemplateid', funneltemplateid,
               'campaignidrt', campaignidrt,
               'adGroups', (
                    SELECT JSON_AGG(
                        JSON_BUILD_OBJECT(
                           'adgroupid', report_adgroups.adgroupid,
                           'ads', (
                                SELECT JSON_AGG(
                                    JSON_BUILD_OBJECT(
                                       'adid', report_ads.adid,
                                       'adname', report_ads.adname,
                                       'adgroupname', report_ads.adgroupname,
                                       'campaignname', report_ads.campaignname,
                                       'date', report_ads.date,
                                       'total_spent', report_ads.total_spent,
                                       'total_click', report_ads.total_click,
                                       'total_impressions', report_ads.total_impressions,
                                       'revenue', report_ads.revenue,
                                       'estimate', report_ads.estimate,
                                       'feedsource', report_ads.feedsource,
                                       'clickdetail', (
                                            SELECT JSON_AGG(
                                                JSON_BUILD_OBJECT(
                                                   'track_id', report_ads_clickdetail.track_id,
                                                   'domain_id', report_ads_clickdetail.domain_id
                                                )
                                            )
                                            FROM report_ads_clickdetail
                                            WHERE report_ads_clickdetail.report_ads_id = report_ads.id and report_ads_clickdetail.report_id = reports.id
                                        )
                                    )
                                    FROM report_ads
                                    WHERE report_ads.report_adgroups_id = report_adgroups.id
                                    GROUP BY report_ads.adid
                                )
                            )
                        )
                        FROM report_adgroups
                        WHERE report_adgroups.report_details_id = report_details.id
                        GROUP BY report_adgroups.adgroupid
                    )
                )
            )
        ) AS details
    FROM reports
    LEFT JOIN report_details ON report_details.report_id = reports.id 
    LEFT JOIN report_adgroups ON report_adgroups.report_details_id = report_details.id 
    LEFT JOIN report_ads ON report_ads.report_adgroups_id = report_adgroups.id
    LEFT JOIN report_ads_clickdetail ON report_ads_clickdetail.report_ads_id = report_ads.id
    WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21'
    AND reports.created_at = (SELECT MAX(created_at) FROM reports)  -- Filtering based on the latest created_at value
    GROUP BY reports.date, reports.trafficchannelid, reports.final, reports.compressedreport
    

    In this modified query, I added an additional condition in the WHERE clause to filter the results based on the latest created_at value from the reports table. By using (SELECT MAX(created_at) FROM reports), we are comparing the created_at value of each row with the maximum value present in the entire table. This will select only the row(s) with the latest created_at value without including it in the GROUP BY clause.

  9. User

    Now I have another issue. If for example clickdetail has 28 items in its array, item inside details will be repeated 28 times too. but the items should only be shown once and shouldnt be repeated depending on the click details

  10. Assistant

    To avoid repeating the items based on the number of click details in the clickdetail array, you can move the subquery for clickdetail outside the JSON_BUILD_OBJECT and cross join it with the report_ads table using LEFT JOIN LATERAL. This will ensure that each row in the report_ads table is matched with the corresponding click details. Here's the updated query:

    SELECT 
        reports.date,
        reports.trafficchannelid,
        reports.final,
        reports.compressedreport,
        JSON_AGG(
            JSON_BUILD_OBJECT(
                'campaignid', campaignid,
                'funneltemplateid', funneltemplateid,
                'campaignidrt', campaignidrt,
                'adGroups', (
                    SELECT JSON_AGG(
                        JSON_BUILD_OBJECT(
                            'adgroupid', report_adgroups.adgroupid,
                            'ads', (
                                SELECT JSON_AGG(
                                    JSON_BUILD_OBJECT(
                                        'adid', report_ads.adid,
                                        'adname', report_ads.adname,
                                        'adgroupname', report_ads.adgroupname,
                                        'campaignname', report_ads.campaignname,
                                        'date', report_ads.date,
                                        'total_spent', report_ads.total_spent,
                                        'total_click', report_ads.total_click,
                                        'total_impressions', report_ads.total_impressions,
                                        'revenue', report_ads.revenue,
                                        'estimate', report_ads.estimate,
                                        'feedsource', report_ads.feedsource
                                    )
                                    ) 
                                FROM report_ads
                                WHERE report_ads.report_adgroups_id = report_adgroups.id
                                GROUP BY report_ads.adid,
                                         report_ads.adname,
                                         report_ads.adgroupname,
                                         report_ads.campaignname,
                                         report_ads.date,
                                         report_ads.total_spent,
                                         report_ads.total_click,
                                         report_ads.total_impressions,
                                         report_ads.revenue,
                                         report_ads.estimate,
                                         report_ads.feedsource
                            )
                        )
                     FROM report_adgroups
                     WHERE report_adgroups.report_details_id = report_details.id
                     GROUP BY report_adgroups.adgroupid
                )
             )
        ) AS details
    FROM reports
    LEFT JOIN report_details ON report_details.report_id = reports.id
    LEFT JOIN report_adgroups ON report_adgroups.report_details_id = report_details.id
    LEFT JOIN report_ads ON report_ads.report_adgroups_id = report_adgroups.id
    LEFT JOIN LATERAL (
        SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
                'track_id', report_ads_clickdetail.track_id,
                'domain_id', report_ads_clickdetail.domain_id
            )
        ) AS clickdetail
        FROM report_ads_clickdetail
        WHERE report_ads_clickdetail.report_ads_id = report_ads.id AND report_ads_clickdetail.report_id = reports.id
    ) AS clickdetail ON true
    WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21'
    GROUP BY reports.date, reports.trafficchannelid, reports.final, reports.compressedreport
    

    In this updated query, the subquery for clickdetail is moved outside the JSON_BUILD_OBJECT. It is transformed into a lateral join using LEFT JOIN LATERAL. This allows each row in report_ads to be matched with the corresponding click details. The click details are aggregated separately using JSON_AGG in the lateral subquery. This ensures that the items inside details are not repeated for each click detail.

    Make sure to adjust the grouping in the subquery for report_ads based on the necessary fields to avoid duplicate items.

  11. User

    ERROR: syntax error at or near "FROM" LINE 46: FROM report_adgroups

  12. Assistant

    Apologies for the error in the query. It seems there was a syntax issue. Please try the updated query below:

    SELECT 
        reports.date,
        reports.trafficchannelid,
        reports.final,
        reports.compressedreport,
        JSON_AGG(
            JSON_BUILD_OBJECT