How can SQL be used in the data mining process? (2024)

1 Data preparation

SELECT customer_id, product_category, SUM(order_amount) AS total_spentFROM ordersWHERE order_status <> 'Returned'GROUP BY customer_id, product_categoryORDER BY order_date; 

Help others by sharing more (125 characters min.)

2 Data exploration

-- Descriptive statistics of order amountSELECT AVG(order_amount) AS mean, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_amount) AS median, STDDEV(order_amount) AS std_dev, MAX(order_amount) - MIN(order_amount) AS rangeFROM orders;-- Correlation between order amount and customer ageSELECT CORR(order_amount, customer_age) AS corrFROM orders;-- Frequency of product categorySELECT product_category, COUNT(*) AS freqFROM ordersGROUP BY product_category;-- Contingency table of product category and customer genderSELECT product_category, customer_gender, COUNT(*) AS countFROM ordersGROUP BY product_category, customer_gender; 

Help others by sharing more (125 characters min.)

3 Data modeling

-- Classification of customers into segmentsSELECT customer_id, CASE WHEN total_spent >= 1000 AND freq >= 10 THEN 'High-value loyal' WHEN total_spent >= 1000 AND freq < 10 THEN 'High-value occasional' WHEN total_spent < 1000 AND freq >= 10 THEN 'Low-value loyal' ELSE 'Low-value occasional' END AS segmentFROM ( SELECT customer_id, SUM(order_amount) AS total_spent, COUNT(*) AS freq FROM orders GROUP BY customer_id) AS customer_summary;-- Clustering of products into categoriesSELECT product_id, cluster_idFROM ( SELECT product_id, array_agg(feature) AS features FROM products GROUP BY product_id) AS product_featuresCROSS JOIN ( SELECT cluster_id, array_agg(feature) AS centroids FROM ( SELECT feature, NTILE(4) OVER (ORDER BY feature) AS cluster_id FROM products ) AS product_clusters GROUP BY cluster_id) AS cluster_centroidsORDER BY ABS(features <-> centroids);-- Association rules between productsSELECT itemset, support, confidence, liftFROM ( SELECT itemset, COUNT(*) AS support FROM ( SELECT order_id, array_agg(product_id) AS itemset FROM order_details GROUP BY order_id ) AS order_itemsets GROUP BY itemset) AS itemset_supportJOIN ( SELECT antecedent, consequent, COUNT(*) AS confidence FROM ( SELECT order_id, UNNEST(itemset) AS antecedent, UNNEST(itemset) AS consequent FROM ( SELECT order_id, array_agg(product_id) AS itemset FROM order_details GROUP BY order_id ) AS order_itemsets ) AS order_pairs WHERE antecedent <> consequent GROUP BY antecedent, consequent) AS rule_confidenceON itemset_support.itemset = ARRAY[rule_confidence.antecedent, rule_confidence.consequent]JOIN ( SELECT product_id, COUNT(*) AS freq FROM order_details GROUP BY product_id) AS product_freqON rule_confidence.antecedent = product_freq.product_idORDER BY lift DESC;-- Regression of order amount on customer and product attributesSELECT order_id, order_amount, predicted_amount, residualFROM ( SELECT order_id, order_amount, regr_intercept(order_amount, customer_age) + regr_slope(order_amount, customer_age) * customer_age + regr_slope(order_amount, product_price) * product_price AS predicted_amount FROM orders JOIN customers ON orders.customer_id = customers.customer_id JOIN products ON orders.product_id = products.product_id) AS order_predictionCROSS JOIN ( SELECT regr_r2(order_amount, customer_age) + regr_r2(order_amount, product_price) AS r_squared FROM orders JOIN customers ON orders.customer_id = customers.customer_id JOIN products ON orders.product_id = products.product_id) AS model_fitORDER BY residual;-- Anomaly detection in order amountSELECT order_id, order_amount, z_score, anomalyFROM ( SELECT order_id, order_amount, (order_amount - AVG(order_amount) OVER ()) / STDDEV(order_amount) OVER () AS z_score FROM orders) AS order_z_scoreCROSS JOIN ( SELECT PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY z_score) AS threshold FROM order_z_score) AS z_thresholdORDER BY z_score DESC; 

Help others by sharing more (125 characters min.)

4 Here’s what else to consider

Help others by sharing more (125 characters min.)