Data Science 100 Knocks (Structured Data Processing) – SQL part4 (Q61 to Q80)

Commentary :

This is a SQL query that retrieves data from a database. Let's break it down step by step:

The query begins with the %%sql magic command, which is used in Jupyter notebooks to specify that the following code is SQL code.

The main query selects three columns: customer_id, sum_amount, and log_amount. These columns will be derived from the subquery in parentheses.

The subquery in parentheses first filters out all the records where the customer_id starts with the letter "Z". This is done with the NOT LIKE 'Z%' condition.

The filtered records are then grouped by customer_id using the GROUP BY clause.

The SUM function is used to calculate the total amount for each customer_id.

The AS sum_amount clause renames the calculated column to sum_amount.

The subquery is then aliased as sum_amount_tbl.

Finally, the outer query selects the customer_id, sum_amount, and the logarithm (with base e) of the sum_amount plus 0.5. This is calculated using the LOG function and aliased as log_amount.

The results are limited to 10 rows with the LIMIT clause.

Commentary :

This is a SQL query that retrieves data from a database. Let's break it down step by step:

The query begins with the %%sql magic command, which is used in Jupyter notebooks to specify that the following code is SQL code.

The main query selects three columns: customer_id, sum_amount, and log_amount. These columns will be derived from the subquery in parentheses.

The subquery in parentheses first filters out all the records where the customer_id starts with the letter "Z". This is done with the NOT LIKE 'Z%' condition.

The filtered records are then grouped by customer_id using the GROUP BY clause.

The SUM function is used to calculate the total amount for each customer_id.

The AS sum_amount clause renames the calculated column to sum_amount.

The subquery is then aliased as sum_amount_tbl.

Finally, the outer query selects the customer_id, sum_amount, and the natural logarithm (with base e) of the sum_amount plus 0.5. This is calculated using the LN function and aliased as log_amount.

The results are limited to 10 rows with the LIMIT clause.

Commentary :

This is a SQL query that retrieves data from a database. Let's break it down step by step:

The query begins with the %%sql magic command, which is used in Jupyter notebooks to specify that the following code is SQL code.

The main query selects four columns: product_cd, unit_price, unit_cost, and unit_profit. These columns will be derived from the product table.

The FROM clause specifies the product table.

The LIMIT clause limits the number of rows returned by the query to 10.

The query calculates the unit_profit by subtracting the unit_cost from the unit_price. This is done using the - operator.

The results of the query include the product_cd, unit_price, unit_cost, and unit_profit for the first 10 rows in the product table.

Overall, this query retrieves information about the price, cost, and profit for a selection of products.

Commentary :

This code is using SQL to query the average unit profit rate for the top 10 products in a database table called "product". Here is an explanation of each part of the code:

"%%sql" is a Jupyter notebook magic command that tells the notebook to interpret the following code as SQL.

"SELECT" is a SQL keyword that indicates that we want to retrieve data from the database.

"AVG" is a SQL function that calculates the average of a given set of values.

"((unit_price * 1.0 - unit_cost) / unit_price)" is a formula that calculates the unit profit rate for each product. It subtracts the unit cost from the unit price and divides the result by the unit price. The multiplication by 1.0 is done to ensure that the division operation returns a floating-point value, rather than an integer.

"AS unit_profit_rate" assigns the calculated value to a column called "unit_profit_rate".

"FROM product" specifies that we want to retrieve data from the "product" table.

"LIMIT 10" limits the number of rows returned to 10, meaning we only want to see the results for the top 10 products by some criteria (which is not specified in the code snippet).

Commentary :

This code is using SQL to query a modified version of a database table called "product". The modification involves calculating a new price for each product based on its unit cost, and then using this new price to calculate a new profit rate. Here is an explanation of each part of the code:

"%%sql" is a Jupyter notebook magic command that tells the notebook to interpret the following code as SQL.

"WITH new_price_tbl AS (...)" sets up a Common Table Expression (CTE) called "new_price_tbl". This allows us to define a subquery that can be used in the subsequent query.

"( SELECT product_cd, unit_price, unit_cost, TRUNC(unit_cost / 0.7) AS new_price FROM product )" is a subquery that selects data from the "product" table, calculates a new price based on the unit cost, and assigns it to a new column called "new_price". The "TRUNC" function is used to round the calculated new price down to the nearest integer, based on the assumption that the company wants to price their products at a whole number value.

"SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10 ;" is the main query that selects all columns from the "new_price_tbl" CTE and calculates a new profit rate for each product. The new profit rate is calculated by subtracting the unit cost from the new price, dividing the result by the new price, and assigning it to a new column called "new_profit_rate". The "LIMIT 10" clause limits the number of rows returned to 10, meaning we only want to see the results for the top 10 products by some criteria (which is not specified in the code snippet).

Commentary :

This code is written in SQL and is used to select data from a table named 'product' using a common table expression (CTE) named 'new_price_tbl'. The code calculates the new price for each product by dividing its unit cost by 0.7, and then calculates the new profit rate by subtracting the unit cost from the new price and dividing the result by the new price.

Here is a step-by-step breakdown of the code:

WITH new_price_tbl AS (...): This defines a common table expression (CTE) named 'new_price_tbl' that is used to temporarily store the data from the SELECT statement enclosed in parentheses.

SELECT product_cd, unit_price, unit_cost, ROUND(unit_cost / 0.7) AS new_price FROM product: This selects the product code, unit price, unit cost, and calculates the new price for each product by dividing its unit cost by 0.7 and rounding the result.

SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10: This selects all columns from the 'new_price_tbl' CTE, and calculates the new profit rate by subtracting the unit cost from the new price and dividing the result by the new price. Finally, it limits the output to the first 10 rows.

In summary, this code calculates the new price and new profit rate for each product by using a CTE to temporarily store the necessary data and performing arithmetic operations on that data.

Commentary :

This code is similar to the previous one, but instead of rounding the new price, it is rounded up to the nearest integer using the CEIL function.

Here is a step-by-step breakdown of the code:

WITH new_price_tbl AS (...): This defines a common table expression (CTE) named 'new_price_tbl' that is used to temporarily store the data from the SELECT statement enclosed in parentheses.

SELECT product_cd, unit_price, unit_cost, CEIL(unit_cost / 0.7) AS new_price FROM product: This selects the product code, unit price, unit cost, and calculates the new price for each product by dividing its unit cost by 0.7 and rounding up the result to the nearest integer using the CEIL function.

SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10: This selects all columns from the 'new_price_tbl' CTE, and calculates the new profit rate by subtracting the unit cost from the new price and dividing the result by the new price. Finally, it limits the output to the first 10 rows.

In summary, this code calculates the new price and new profit rate for each product by using a CTE to temporarily store the necessary data and performing arithmetic operations on that data. However, the new price is rounded up to the nearest integer using the CEIL function instead of being rounded as in the previous code.

Commentary :

This code is written in SQL and is used to select data from a table named 'product'. The code selects the product code, unit price, and calculates the tax-inclusive price for each product by multiplying its unit price by 1.1 and truncating the result to remove any decimal places.

Here is a step-by-step breakdown of the code:

SELECT product_cd, unit_price, TRUNC(unit_price * 1.1) AS tax_price FROM product: This selects the product code, unit price, and calculates the tax-inclusive price for each product by multiplying its unit price by 1.1 and truncating the result to remove any decimal places using the TRUNC function.

FROM product: This specifies the 'product' table from which the data is being selected.

LIMIT 10: This limits the output to the first 10 rows.

In summary, this code calculates the tax-inclusive price for each product by multiplying its unit price by 1.1 and truncating the result to remove any decimal places using the TRUNC function.

Commentary :

This code is written in SQL and is used to select data from two tables named 'receipt' and 'product' using two common table expressions (CTEs) named 'amount_all' and 'amount_07'. The code calculates the sum of amounts spent by each customer and the sum of amounts spent on products with a major category code of '07'. It then joins the two CTEs and calculates the sales rate of category '07' products for each customer.

Here is a step-by-step breakdown of the code:

WITH amount_all AS(...): This defines a common table expression (CTE) named 'amount_all' that is used to temporarily store the data from the SELECT statement enclosed in parentheses.

SELECT customer_id, SUM(amount) AS sum_all FROM receipt GROUP BY customer_id: This selects the customer ID and the sum of the amounts spent by each customer from the 'receipt' table, and groups the result by the customer ID.

, amount_07 AS(...): This defines another CTE named 'amount_07' that is used to temporarily store the data from the SELECT statement enclosed in parentheses.

SELECT r.customer_id, SUM(r.amount) AS sum_07 FROM receipt r JOIN product p ON r.product_cd = p.product_cd WHERE p.category_major_cd = '07' GROUP BY customer_id: This selects the customer ID and the sum of the amounts spent on products with a major category code of '07' from the 'receipt' and 'product' tables, joins the two tables on the product code, filters the products by the major category code of '07', and groups the result by the customer ID.

SELECT amount_all.customer_id, sum_all, sum_07, sum_07 * 1.0 / sum_all AS sales_rate FROM amount_all JOIN amount_07 ON amount_all.customer_id = amount_07.customer_id LIMIT 10: This selects the customer ID, the sum of all amounts spent by each customer, the sum of amounts spent on category '07' products by each customer, and the sales rate of category '07' products for each customer by dividing the sum of amounts spent on category '07' products by the sum of all amounts spent by the customer. Finally, it joins the two CTEs on the customer ID and limits the output to the first 10 rows.

In summary, this code calculates the sales rate of category '07' products for each customer by first calculating the sum of all amounts spent by each customer and the sum of amounts spent on category '07' products by each customer, and then dividing the latter by the former. It does so by using two CTEs to temporarily store the necessary data and performing arithmetic operations and joins on that data.

Commentary :

This code is written in SQL and is used to select data from two tables named 'receipt' and 'customer' using a common table expression (CTE) named 'receipt_distinct'. The code calculates the number of days that elapsed between the customer's application date and the date of a distinct sale made by the customer.

Here is a step-by-step breakdown of the code:

WITH receipt_distinct AS(...): This defines a common table expression (CTE) named 'receipt_distinct' that is used to temporarily store the data from the SELECT statement enclosed in parentheses.

SELECT distinct customer_id, sales_ymd FROM receipt: This selects the distinct customer ID and sales year-month-day from the 'receipt' table.

SELECT c.customer_id, r.sales_ymd, c.application_date, EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') - TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_days FROM receipt_distinct r JOIN customer c ON r.customer_id = c.customer_id LIMIT 10: This selects the customer ID, sales year-month-day, application date, and the elapsed number of days between the customer's application date and the date of a distinct sale made by the customer. The calculation of the elapsed days involves converting the sales year-month-day and application date to timestamps and then computing the difference between them in days. Finally, it joins the 'receipt_distinct' CTE with the 'customer' table on the customer ID and limits the output to the first 10 rows.

In summary, this code calculates the number of days that elapsed between a customer's application date and the date of a distinct sale made by the customer by first selecting the distinct customer ID and sales year-month-day from the 'receipt' table, and then joining it with the 'customer' table to obtain the customer's application date. It then performs a calculation involving the conversion of the sales year-month-day and application date to timestamps and the computation of the difference between them in days. It does so by using a CTE to temporarily store the necessary data and performing arithmetic operations and joins on that data.

Commentary :

This code is written in SQL and is used to select data from two tables named 'receipt' and 'customer' using two common table expressions (CTEs) named 'receipt_distinct' and 'time_age_tbl'. The code calculates the elapsed number of months between the customer's application date and the date of a distinct sale made by the customer.

Here is a step-by-step breakdown of the code:

WITH receipt_distinct AS (...): This defines a common table expression (CTE) named 'receipt_distinct' that is used to temporarily store the data from the SELECT statement enclosed in parentheses.

SELECT DISTINCT customer_id, sales_ymd FROM receipt: This selects the distinct customer ID and sales year-month-day from the 'receipt' table.

WITH time_age_tbl AS (...): This defines another CTE named 'time_age_tbl' that is used to temporarily store the data from the SELECT statement enclosed in parentheses.

SELECT c.customer_id, r.sales_ymd, c.application_date, AGE(TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'), TO_TIMESTAMP(c.application_date, 'YYYYMMDD')) AS time_age FROM receipt_distinct r JOIN customer c ON r.customer_id = c.customer_id: This selects the customer ID, sales year-month-day, application date, and the elapsed time between the customer's application date and the date of a distinct sale made by the customer. The calculation of the elapsed time involves converting the sales year-month-day and application date to timestamps and then computing the difference between them using the AGE function. Finally, it joins the 'receipt_distinct' CTE with the 'customer' table on the customer ID.

SELECT customer_id, sales_ymd, application_date, EXTRACT(YEAR FROM time_age) * 12 + EXTRACT(MONTH FROM time_age) AS elapsed_months FROM time_age_tbl LIMIT 10: This selects the customer ID, sales year-month-day, application date, and the elapsed number of months between the customer's application date and the date of a distinct sale made by the customer. The calculation of the elapsed months involves extracting the year and month components of the elapsed time and converting them to months. Finally, it limits the output to the first 10 rows.

In summary, this code calculates the elapsed number of months between a customer's application date and the date of a distinct sale made by the customer by first selecting the distinct customer ID and sales year-month-day from the 'receipt' table, and then joining it with the 'customer' table to obtain the customer's application date. It then calculates the elapsed time between the sales year-month-day and application date using a CTE and the AGE function, and converts the result to months. It does so by using two CTEs to temporarily store the necessary data and performing arithmetic operations and joins on that data.

Commentary :

This SQL code retrieves the customer ID, sales date, and application date of customers who have made purchases, and calculates the number of elapsed years between the application date and the sales date.

The code starts by creating a common table expression called receipt_distinct, which selects distinct combinations of customer ID and sales date from the receipt table. This ensures that each customer is only counted once, even if they made multiple purchases.

The main query then selects the customer ID, sales date, and application date from the receipt_distinct table, and uses the EXTRACT function to calculate the number of elapsed years between the application date and the sales date. The AGE function is used to calculate the time difference between the two dates, and the EXTRACT function is used to extract the year component from the resulting interval.

The result is a table that shows the customer ID, sales date, application date, and number of elapsed years for each customer. The LIMIT clause is used to restrict the output to the first 10 rows of the result set.

Commentary :

This SQL code retrieves the customer ID, sales date, and application date of customers who have made purchases, and calculates the number of elapsed seconds between the application date and the sales date.

The code starts by creating a common table expression called receipt_distinct, which selects distinct combinations of customer ID and sales date from the receipt table. This ensures that each customer is only counted once, even if they made multiple purchases.

The main query then selects the customer ID, sales date, and application date from the receipt_distinct table, and uses the EXTRACT function along with TO_TIMESTAMP function to calculate the number of elapsed seconds between the application date and the sales date. The EXTRACT(EPOCH FROM <interval>) function is used to extract the number of seconds from the resulting interval.

The result is a table that shows the customer ID, sales date, application date, and number of elapsed seconds for each customer. The LIMIT clause is used to restrict the output to the first 10 rows of the result set.

Commentary :

This SQL code starts by defining a Common Table Expression (CTE) named elapsed_days_tbl, which extracts the day of the week (as a number from 0 to 6, where 0 is Sunday) for each sales_ymd date in the receipt table.

In the main query, sales_ymd and elapsed_days are selected from the elapsed_days_tbl CTE. Then, the code calculates a new date by subtracting the elapsed_days from sales_ymd. This is done by casting the elapsed_days as an integer and subtracting it from sales_ymd. The resulting date is the Monday of the week corresponding to the sales_ymd date.

The LIMIT 10 clause at the end of the code limits the output to the first 10 rows.

Commentary :

This code is using the SQL programming language to select a random subset of customers from the "customer" table.

The code first creates a temporary table called "customer_tmp" by selecting all columns from the "customer" table and adding two additional columns using the ROW_NUMBER and COUNT functions.

The ROW_NUMBER function assigns a unique integer to each row in the table, based on the result of the ORDER BY RANDOM() clause. This effectively randomizes the order of the rows in the table.

The COUNT function with the OVER() clause counts the total number of rows in the table, and creates a new column called "cnt" that contains this count for each row.

The main SELECT statement then selects a subset of customers from the temporary table by filtering on the "row" column, which was created by the ROW_NUMBER function. Specifically, it selects the first 1% of rows, as determined by the "cnt" column created by the COUNT function.

The final clause, "LIMIT 10", limits the results to the first 10 rows of the subset, effectively returning a random sample of 10 customers from the original "customer" table.

Overall, this code is a simple way to select a random sample of customers from a large table for testing or exploratory analysis purposes. The sampling percentage (in this case, 1%) can be adjusted to create larger or smaller samples.

Commentary :

This code is using the SQL programming language to randomly select a subset of customers from the "customer" table, grouping them by their gender and returning the number of customers in each gender group.

The code first creates a temporary table called "cusotmer_random" by selecting all columns from the "customer" table and adding two additional columns using the ROW_NUMBER and COUNT functions.

The ROW_NUMBER function assigns a unique integer to each row in the table, based on the result of the ORDER BY RANDOM() clause. This effectively randomizes the order of the rows in the table.

The PARTITION BY gender_cd clause in the ROW_NUMBER function ensures that the random ordering is done separately for each gender group.

The COUNT function with the OVER(PARTITION BY gender_cd) clause counts the total number of rows in each gender group, and creates a new column called "cnt" that contains this count for each row.

The main SELECT statement then selects a subset of customers from the temporary table by filtering on the "rn" column, which was created by the ROW_NUMBER function. Specifically, it selects the first 10% of rows in each gender group, as determined by the "cnt" column created by the COUNT function.

The WHERE clause filters the rows in the "cusotmer_random" table to only include those with an "rn" value less than or equal to 10% of the "cnt" value for their respective gender group.

Finally, the main SELECT statement groups the rows by gender_cd and returns the count of rows in each group, effectively giving the number of customers in each gender group in the random sample.

Overall, this code is a way to randomly select a subset of customers from a large table while ensuring that the sample includes a similar proportion of each gender group as the original table. The sampling percentage (in this case, 10%) can be adjusted to create larger or smaller samples.

Commentary :

This code is using the SQL programming language to identify customers whose total purchase amount falls significantly outside the norm, based on their logarithmically transformed purchase amount.

The code first creates a temporary table called "sales_amount" by selecting the customer_id and sum of purchase amount from the "receipt" table, grouped by customer_id. The sum of purchase amount is also transformed into a logarithmic scale using the LN function, which adds 0.5 to avoid taking the logarithm of zero.

The main SELECT statement then selects the customer_id, sum_amount, and log_sum_amount columns from the temporary table. It also uses a CROSS JOIN to join in another temporary table called "stats_amount", which is created by selecting the average and population standard deviation of the log_sum_amount column from the "sales_amount" table.

The WHERE clause filters the rows in the "sales_amount" table to only include those where the absolute difference between the log_sum_amount and the average log_sum_amount, divided by the standard deviation of the log_sum_amount, is greater than 3. This is a way to identify customers whose purchase amounts are more than three standard deviations from the mean, which is a common threshold for identifying outliers in statistical analysis.

Finally, the main SELECT statement limits the output to 10 rows.

Overall, this code is a way to identify customers who have made significantly large or small purchases, relative to the average purchase amount in the dataset, by transforming the purchase amounts into a logarithmic scale and applying a statistical outlier detection criterion.

Commentary :

This code is using the SQL programming language to query a database. Specifically, it is using a common table expression (CTE) to create a temporary table called "sales_amount", which calculates the total amount of sales for each customer in the "receipt" table.

The WHERE clause of the CTE filters out any customers whose ID starts with the letter "Z". The GROUP BY clause groups the sales by customer ID.

The main query then performs a CROSS JOIN between the "sales_amount" table and a subquery that calculates the 25th and 75th percentiles of the sales amounts using the PERCENTILE_CONT function. These percentiles are stored in a temporary table called "stats_amount".

Finally, the WHERE clause of the main query filters out any sales amounts that are more than 1.5 times the interquartile range (IQR) away from the 25th or 75th percentiles. This is a common method for identifying outliers in a dataset. The results are limited to the top 10 rows.

Overall, this code is querying a database to identify customers with unusually high or low sales amounts compared to their peers, based on their position in the interquartile range.

Commentary :

This code is using the SQL programming language to query a database. Specifically, it is querying the "product" table and using a combination of the SUM and CASE statements to count the number of missing values (NULLs) in each column of the table.

The query selects six columns and gives them aliases that match the names of the columns in the table: "product_cd", "category_major_cd", "category_medium_cd", "category_small_cd", "unit_price", and "unit_cost".

For each column, the CASE statement checks whether the value is NULL, and returns 1 if it is, or 0 otherwise. The SUM function then adds up all the 1s and 0s to give the total number of NULL values in each column.

The LIMIT 10 clause limits the results to the first 10 rows of the table, which is not particularly meaningful in this case since we are only interested in the summary statistics.

Overall, this code is a simple way to check for missing data in the "product" table and get a quick summary of the number of missing values in each column.