1. * **Using GROUP BY + HAVING:** Group records by all selected columns; if `COUNT > 1`, it indicates duplicates. * **Row Number:** Assign row numbers using `ROW_NUMBER() OVER (PARTITION BY ...)`; filter out rows where the row number is greater than 1. * **Joins:** Use self-joins to identify duplicates. 2. Calculate monthly sales running total by product. ```sql SELECT ProductID, SalesDate, SalesAmount, SUM(SalesAmount) OVER ( PARTITION BY ProductID, DATE_TRUNC('month', SalesDate) ORDER BY SalesDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RunningTotal FROM Sales; ``` * Optimizations: * Use indexes on `(ProductID, SalesDate)` for faster sorting. * Use **CLUSTER BY** in Snowflake for efficient scans. * Use **materialized views** if queried frequently. 3. To get totals at multiple levels (per product, per month, and overall): ```sql SELECT ProductID, DATE_TRUNC('month', SalesDate) AS SalesMonth, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY ROLLUP (ProductID, DATE_TRUNC('month', SalesDate)); ``` 4. * By default, aggregate functions like SUM return **NULL** if all values are NULL. * Use **COALESCE** or **IFNULL** to replace NULL with default values (e.g., 0). * **Key Difference:** * `COALESCE` → accepts multiple arguments and returns the first non-null. * `ISNULL` → accepts only two arguments (expression, replacement). Example: ```sql SELECT COALESCE(SUM(OrderAmount), 0) AS SafeTotal FROM Sales;