Grouping With Aggregations 🏆
My SQL\n\nFirst, we group the data by the sell_date column. This allows us to count the number of unique products sold on each sell date, which we store in the 
# My SQL\n\nFirst, we group the data by the `sell_date` column. This allows us to count the number of unique products sold on each sell date, which we store in the `num_sold` column.\n\nThe most challenging part is to sort and join all unique product names in each group to get the products column. We can use the `GROUP_CONCAT()` function to combine multiple values from multiple rows into a single string. The syntax of the `GROUP_CONCAT()` function is as follows:\n```\nGROUP_CONCAT(DISTINCT [column_name] [separator])\n```\nThe `DISTINCT` keyword ensures that only unique values are concatenated. The separator parameter specifies the character that should be used to separate the values.\n\n``` sql []\n# Write your MySQL query statement below\nSELECT sell_date, COUNT(DISTINCT(product)) as num_sold,\nGROUP_CONCAT(\n    DISTINCT product \n    ORDER BY product \n    SEPARATOR \',\'\n) AS products\nFROM Activities\nGROUP BY sell_date\nORDER BY sell_date ASC\n```\n\n# Pandas\n\nThe question asks us to group and summarize data by date. To do this, we first use the `groupby` function to group the `DataFrame` activities by date. This creates a new object called `groups`, which is a `DataFrameGroupBy` object.\n\nOnce we have the `DataFrameGroupBy` object, we can use the `agg()` function to perform aggregation operations on each group. The `agg()` function takes a list of aggregation tasks to perform. In this case, we are specifying two aggregation tasks:\n- Creating num_solid column with the number of unique products sold on each sell date. \n`num_sold=(\'product\', \'nunique\')`\n- Join all unique names within each group. \n`products=(\'product\', lambda x: \',\'.join(sorted(set(x))))`\n\n``` python3 []\nimport pandas as pd\n\ndef categorize_products(activities: pd.DataFrame) -> pd.DataFrame:\n  groups = activities.groupby(\'sell_date\')\n\n  stats = groups.agg(\n    num_sold=(\'product\', \'nunique\'),\n    products=(\'product\', lambda x: \',\'.join(sorted(set(x))))\n  ).reset_index()\n  stats.sort_values(\'sell_date\', inplace=True)\n  return stats\n```
['Python', 'Python3', 'MySQL', 'Pandas']