You are analyzing customer transaction data in Snowflake to identify fraudulent activities. The 'TRANSACTION AMOUNT' column exhibits a right-skewed distribution. Which of the following Snowflake queries is MOST effective in identifying outliers based on the Interquartile Range (IQR) method, specifically targeting unusually large transaction amounts? Assume IQR is already calculated as variable and QI as and Q3 as in snowflake session.

• A. SELECT TRANSACTION ID FROM TRANSACTIONS WHERE TRANSACTION_AMOUNT > (SELECT WITHIN GROUP (ORDER BY TRANSACTION_AMOUNT) FROM TRANSACTIONS);
• B. SELECT TRANSACTION ID FROM TRANSACTIONS WHERE TRANSACTION_AMOUNT > (SELECT MEDIAN(TRANSACTION AMOUNT) FROM TRANSACTIONS);
• C. SELECT TRANSACTION ID FROM TRANSACTIONS WHERE TRANSACTION AMOUNT > q3 + (1.5 iqr);
• D. SELECT TRANSACTION ID FROM TRANSACTIONS WHERE TRANSACTION_AMOUNT < qi - (1.5 iqr);
• E. SELECT TRANSACTION ID FROM TRANSACTIONS WHERE TRANSACTION_AMOUNT > (SELECT + 3 FROM TRANSACTIONS);

說明：（僅 KaoGuTi 成員可見）

A data scientist is analyzing sales data in Snowflake to identify seasonal trends. The 'SALES TABLE' contains columns 'SALE DATE' (DATE) and 'SALE _ AMOUNT' (NUMBER). They want to calculate the average daily sales amount for each month and year in the dataset. Which of the following SQL queries will correctly achieve this, while also handling potential NULL values in 'SALE AMOUNT?

• A. Option D
• B. Option E
• C. Option A
• D. Option C
• E. Option B

說明：（僅 KaoGuTi 成員可見）

You are using Snowpark for Python to build a feature engineering pipeline for a machine learning model that predicts customer churn. The data is stored in a Snowflake table called 'CUSTOMER DATA' , and you want to create new features based on time-series data within the table. You need to calculate the 'Recency' feature (days since the last transaction) and 'Frequency' feature (number of transactions in the last 3 months). Considering performance and best practices, which Snowpark approach would you choose?

• A. Write custom Python code in a Snowpark UDF to retrieve each transaction for a customer and calculate recency and frequency directly in Python without pandas.
• B. Create a Python UDF using Pandas to calculate 'Recency' and 'Frequency'. Apply this UDF to the 'CUSTOMER DATA' table through Snowpark, processing the data row by row.
• C. Use Snowpark DataFrame API to perform window functions within Snowflake to calculate 'Recency' and 'Frequency' directly, leveraging Snowflake's processing power without transferring data to the client.
• D. Fetch the entire 'CUSTOMER DATA table into a Pandas DataFrame using , then use Pandas' time-series functions to calculate 'Recency' and 'Frequency'. After feature engineering, load the Pandas DataFrame back into Snowflake.
• E. Write a stored procedure in SQL that calculates 'Recency' and 'Frequency' using SQL window functions, and then call this stored procedure from your Snowpark Python code.

說明：（僅 KaoGuTi 成員可見）

You have a table 'PRODUCT SALES in Snowflake with columns: 'PRODUCT (INT), 'SALE_DATE (DATE), 'SALES_AMOUNT (FLOAT), and 'PROMOTION FLAG' (BOOLEAN). You need to perform the following data preparation steps using Snowpark SQLAPI:

• A. Handling missing 'SALES_AMOUNT values by imputing them with the average 'SALES_AMOUNT' for the same 'PRODUCT_ID during the previous month. If there's no data for the previous month, use the overall average for that
• B. Creating a new feature representing the percentage change in 'SALES_AMOUNT compared to the previous day for the same 'PRODUCT_ID. Handle the first day of each 'PRODUCT by setting 'SALES_GROWTH' to O.
• C. Creating a feature that returns 1 if there is a PROMOTION_FLAG of True and SALES_AMOUNT > 1000, and zero otherwise
• D. All of the above.
• E. Converting 'SALE_DATE to a quarterly representation (e.g., '2023-QI').

說明：（僅 KaoGuTi 成員可見）

A marketing analyst is building a propensity model to predict customer response to a new product launch. The dataset contains a 'City' column with a large number of unique city names. Applying one-hot encoding to this feature would result in a very high-dimensional dataset, potentially leading to the curse of dimensionality. To mitigate this, the analyst decides to combine Label Encoding followed by binarization techniques. Which of the following statements are TRUE regarding the benefits and challenges of this combined approach in Snowflake compared to simply label encoding?

• A. Label encoding followed by binarization will reduce the memory required to store the 'City' feature compared to one-hot encoding, and Snowflake's columnar storage optimizes storage for integer data types used in label encoding.
• B. Binarization following label encoding may enhance model performance if a specific split based on a defined threshold is meaningful for the target variable (e.g., distinguishing between cities above/below a certain average income level related to marketing success).
• C. Binarizing a label encoded column using a simple threshold (e.g., creating a 'high_city_id' flag) addresses the curse of dimensionality by reducing the number of features to one, but it loses significant information about the individual cities.
• D. Label encoding introduces an arbitrary ordinal relationship between the cities, which may not be appropriate. Binarization alone cannot remove this artifact.
• E. While label encoding itself adds an ordinal relationship, applying binarization techniques like binary encoding (converting the label to binary representation and splitting into multiple columns) after label encoding will remove the arbitrary ordinal relationship.

說明：（僅 KaoGuTi 成員可見）