Pandas, Polars, PostgreSQL
RememberThis table may contain duplicates rows.\n# Pandas\npy []\nimport pandas as pd\n\ndef find_customers(customer: pd.DataFrame, product: pd.DataFrame) -> pd
- Remember`This table may contain duplicates rows.`\n# Pandas\n```py []\nimport pandas as pd\n\ndef find_customers(customer: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:\n    return ( customer.groupby(by=\'customer_id\', as_index=False, sort=False)\n            .agg(product_numbers = (\'product_key\', \'nunique\'))\n            .query("product_numbers == @product.size")\n            .filter(items=[\'customer_id\'], axis=1) # returns dataframe even with one column.\n           )\n```\n- SEEEEE? WE CAN USE FUNCTIONS INSIDE `query()` using `@`. I did it accidentally! You can even write `df[\'a\'].min()` something like this too!\n```py []\nimport pandas as pd\n\ndef find_customers(customer: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:\n    return ( customer.drop_duplicates()\n            .groupby(by=\'customer_id\', as_index=False, sort=False)\n            .agg(product_numbers = (\'product_key\', \'size\'))\n            .query("product_numbers == @product.size")\n            .filter(items=[\'customer_id\'], axis=1)\n           )\n```\n\n# Polars\n```js\nimport polars as pl\nfrom polars import col\n\ndef find_customers(customer: pl.LazyFrame, product: pl.LazyFrame) -> pl.LazyFrame:\n    product_size = product.select(col(\'product_key\').len()) .collect()\n\n    return ( customer.group_by(\'customer_id\')\n            .agg( product_numbers = col(\'product_key\').n_unique() )\n            .filter( col(\'product_numbers\') == product_size ) // product_size is a subquery, so need to write .collect() UP there\n            .select( col(\'customer_id\') )\n           )\n\nfind_customers(pl.LazyFrame(customer), pl.LazyFrame(product)) .collect()\n```\n\n# PostgreSQL (Execution Order : FROM -> GROUP BY -> HAVING -> SELECT)\n- In Pandas and Polars we `filtered again on the GROUP BY(and agg()) result`. In PostgreSQL we filter on the `GROUP BY result` through `HAVING` clause.\n```postgresql\n-- Write your PostgreSQL query statement below\nSELECT customer_id\nFROM customer\nGROUP BY customer_id\nHAVING COUNT(DISTINCT product_key) = (SELECT COUNT(product_key) FROM product);\n```\n\n```js\n        You cant REFERENCE an ALIAS from CTE in HAVING clause :\n```\n![image.png](https://assets.leetcode.com/users/images/60901a20-8c6a-4a05-bc22-cf55baf03594_1730885931.3530004.png)\n\n
['PostgreSQL', 'Pandas']