with_company_id.sql

SELECT   s0."age_id",
         Count(s0."id"),
         s0."age_bucket"
FROM     (
                    SELECT     sm0."id" AS "id",
                               CASE
                                          WHEN age < 1 THEN 1
                                          WHEN age < 7 THEN 7
                                          WHEN age < 31 THEN 31
                                          ELSE NULL
                               END AS "age_id",
                               CASE
                                          WHEN age < 1 THEN 'today'
                                          WHEN age < 7 THEN 'last 7 days'
                                          WHEN age < 31 THEN 'last 31 days'
                                          ELSE 'greater than 31 days'
                               END                        AS "age_bucket"
                    FROM       "mv_listings_consolidated" AS sm0
                    INNER JOIN
                               (
                                               SELECT DISTINCT
                                               ON (
                                                                               sss0."id")
                                                               CASE
                                                                               WHEN sss4."id" IS NOT NULL THEN 'Allowed'
                                                                               WHEN sss2."id" IS NOT NULL
                                                                               OR              sss3."id" IS NOT NULL THEN 'Action in progress'
                                                                               ELSE 'Greylist'
                                                               END       AS "status",
                                                               sss0."id" AS "id"
                                               FROM            "stores"  AS sss0
                                               INNER JOIN
                                                               (
                                                                        SELECT   sssm0."store_id"           AS "store_id",
                                                                                 sssm0."company_id"         AS "company_id"
                                                                        FROM     "mv_listings_consolidated" AS sssm0
                                                                        WHERE    (
                                                                                          sssm0."company_id" = $1)
                                                                        GROUP BY sssm0."store_id",
                                                                                 sssm0."company_id") AS sss1
                                               ON              sss1."store_id" = sss0."id"
                                               LEFT OUTER JOIN
                                                               (
                                                                               SELECT DISTINCT
                                                                               ON (
                                                                                                               ssse0."company_id", ssse0."store_id", ssse0."created_at") ssse0."id" AS "id",
                                                                                               ssse0."company_id"                                                                   AS "company_id",
                                                                                               ssse0."store_id"                                                                     AS "store_id",
                                                                                               ssse0."enforcement_statuses_id"                                                      AS "enforcement_statuses_id"
                                                                               FROM            "enforcements"                                                                       AS ssse0
                                                                               WHERE           (
                                                                                                               ssse0."enforcement_statuses_id" IN (1,2,3,4))
                                                                               ORDER BY        ssse0."company_id",
                                                                                               ssse0."store_id",
                                                                                               ssse0."created_at",
                                                                                               ssse0."created_at" DESC) AS sss2
                                               ON              (
                                                                               sss2."company_id" = sss1."company_id")
                                               AND             (
                                                                               sss2."store_id" = sss1."store_id")
                                               LEFT OUTER JOIN
                                                               (
                                                                               SELECT DISTINCT
                                                                               ON (
                                                                                                               ssst0."company_id", ssst0."store_id", ssst0."created_at") ssst0."id" AS "id",
                                                                                               ssst0."company_id"                                                                   AS "company_id",
                                                                                               ssst0."store_id"                                                                     AS "store_id",
                                                                                               ssst0."status_id"                                                                    AS "status_id"
                                                                               FROM            "test_purchases"                                                                     AS ssst0
                                                                               WHERE           (
                                                                                                               ssst0."status_id" IN (1,2))
                                                                               ORDER BY        ssst0."company_id",
                                                                                               ssst0."store_id",
                                                                                               ssst0."created_at",
                                                                                               ssst0."created_at" DESC) AS sss3
                                               ON              (
                                                                               sss3."company_id" = sss1."company_id")
                                               AND             (
                                                                               sss3."store_id" = sss1."store_id")
                                               LEFT OUTER JOIN
                                                               (
                                                                               SELECT DISTINCT
                                                                               ON (
                                                                                                               sssw0."company_id", sssw0."store_id", sssw0."until") sssw0."id" AS "id",
                                                                                               sssw0."until"                                                                   AS "until",
                                                                                               sssw0."company_id"                                                              AS "company_id",
                                                                                               sssw0."store_id"                                                                AS "store_id"
                                                                               FROM            "whitelist"                                                                     AS sssw0
                                                                               WHERE           (
                                                                                                               sssw0."until" >= Date(Now()))) AS sss4
                                               ON              (
                                                                               sss4."company_id" = sss1."company_id")
                                               AND             (
                                                                               sss4."store_id" = sss1."store_id")) AS ss1
                    ON         sm0."store_id" = ss1."id"
                    WHERE      (
                                          sm0."company_id" = $2::bigint)
                    AND        (
                                          To_tsvector('english', sm0."product_name") @@ websearch_to_tsquery($3))
                    and        (
                                          sm0."age" < 31)) AS s0
GROUP BY s0."age_bucket",
         s0."age_id"
ORDER BY s0."age_id" [14, 14, "Fender 30th Anniversary"]