Created
August 26, 2022 18:46
-
-
Save aarkerio/06eaa46274d06648bfd4960c5366ee8f to your computer and use it in GitHub Desktop.
PGSQL Materialized view
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE MATERIALIZED VIEW products_shopify_ids_on_active_shops AS | |
SELECT shopify_domain, array_agg(col ORDER BY col) AS product_shopify_ids | |
FROM ( | |
SELECT DISTINCT | |
shops.shopify_domain AS shopify_domain, | |
unnest(offers.offerable_product_shopify_ids) AS col | |
FROM shops | |
INNER JOIN offers | |
ON shops.id = offers.shop_id | |
INNER JOIN subscriptions | |
ON shops.id = subscriptions.shop_id | |
WHERE shops.uninstalled_at IS NULL AND | |
shops.shopify_token IS NOT NULL AND | |
(subscriptions.plan_id = 19 OR subscriptions.plan_id = 12) | |
) t | |
GROUP BY shopify_domain ORDER BY shopify_domain; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment