WITH "count_shp_packs" AS (SELECT "shp_pack_id", COUNT(*) AS COUNT
                           FROM "ord_items_metadata"
                           WHERE "free_fill_used" = TRUE
                             AND created_at >= now() - INTERVAL '30 days'
                           GROUP BY "shp_pack_id")
SELECT DISTINCT "product_id", COUNT(*)
FROM "prd_versions"
       JOIN shp_packs USING (prd_ver_id)
       LEFT JOIN count_shp_packs ON count_shp_packs.shp_pack_id = shp_packs.shp_pack_id
WHERE "org_warehouse_id" IN (SELECT "ms"."org_warehouse_id"
                             FROM (WITH "activation_spent_warehouses" AS (SELECT "org_warehouse_id",
                                                                                 SUM(oac.activation_cost)    AS sum_activation_cost,
                                                                                 COUNT(ord_item_metadata_id) AS count_ord,
                                                                                 SUM(org_shp_cons_rate)      AS sum_shp_cons_rate
                                                                          FROM "ord_items_metadata" AS "om",
                                                                               "org_warehouse_activation_cost" AS "oac"
                                                                          WHERE "om"."free_fill_used" = TRUE
                                                                            AND om.bill_weight BETWEEN oac.from_billable_weight AND oac.to_billable_weight
                                                                            AND om.created_at >= now() - INTERVAL '30 days'
                                                                          GROUP BY "om"."org_warehouse_id") SELECT "org_warehouse_settings"."org_warehouse_id",
                                                                                                                   json_extract_path_text(
                                                                                                                       org_warehouse_settings.settings::json,
                                                                                                                       'activation_spent_limit_per_month')::NUMERIC -
                                                                                                                   activation_spent_warehouses.sum_activation_cost AS differ_smc,
                                                                                                                   json_extract_path_text(
                                                                                                                       org_warehouse_settings.settings::json,
                                                                                                                       'max_items_count_per_month')::NUMERIC -
                                                                                                                   activation_spent_warehouses.count_ord           AS differ_cr,
                                                                                                                   json_extract_path_text(
                                                                                                                       org_warehouse_settings.settings::json,
                                                                                                                       'max_shipping_limit_value')::NUMERIC *
                                                                                                                   0.1 -
                                                                                                                   activation_spent_warehouses.sum_shp_cons_rate   AS differ_sr
                                                                                                            FROM "org_warehouse_settings"
                                                                                                                   LEFT JOIN activation_spent_warehouses
                                                                                                                             ON activation_spent_warehouses.org_warehouse_id =
                                                                                                                                org_warehouse_settings.org_warehouse_id
                                                                                                            WHERE
                                                                                                                "org_warehouse_settings"."org_warehouse_id" IN
                                                                                                                (SELECT "org_warehouse_id"
                                                                                                                 FROM "org_warehouse_ord_promotion_con"
                                                                                                                 WHERE
                                                                                                                     "ord_promotion_id" IN
                                                                                                                     (SELECT "ord_promotion_id"
                                                                                                                      FROM "ord_promotions"
                                                                                                                      WHERE "code" = 'free_fill'))) AS "ms"
                             WHERE differ_smc IS NULL
                                OR differ_smc > 0 AND differ_cr IS NULL
                                OR differ_cr > 0 AND differ_sr IS NULL
                                OR differ_sr > 0)
  AND "end_at" IS NULL
  AND "product_not_available" = FALSE
  AND "product_published" = TRUE
  AND "shp_packs"."free_fill" = TRUE
  AND ((free_fill_quota - count_shp_packs.COUNT > 0) OR (free_fill_quota > 0 AND count_shp_packs.COUNT IS NULL))
GROUP BY "product_id"
ORDER BY "count" DESC
LIMIT 100