LC Python 刷题笔记有志者事竟成

cythia123

跟着楼主一起刷几天没看楼主设了密码了。。。

李浩泉

本帖为密码帖，请输入密码

李浩泉

本帖为密码帖，请输入密码

jliu

开篇总结的太好了，靠刷题逆天改命，就是这么简单粗暴

李浩泉

2020-08 BIE 新题

/*
orders: order_id, customer_id, item_id, item_price, item_quantity, order_datetime
customer: customer_id, name, address, zipcode,....
item: item_id, category
Q1: item quantity sold yesterday
*/
SELECT
item_id,
SUM(item_quantity) AS quantity
FROM orders
WHERE dateadd(day,1,order_datetime) = GETDATE()
GROUP BY item_id

复制代码

李浩泉

/*
orders: order_id, customer_id, item_id, item_price, item_quantity, order_datetime
customer: customer_id, name, address, zipcode,....
item: item_id, category
Q2: return customer id who bought item yesterday but didn't buy item today
*/
SELECT
customer_id
FROM orders
WHERE dateadd(day,1,order_datetime) = GETDATE()
AND customer_id NOT IN
(
SELECT
customer_id
FROM orders
WHERE order_datetime = GETDATE()
)

复制代码

李浩泉

/*
Q3: calculate each category item quantity sold by three types of item price : item_price <20, item price between 20 and 50, item price>50
results should be: category |unit0-20 |unit20-50 | unit50
*/
WITH CTE AS
(
SELECT
category,
item.item_id,
SUM(ISNULL(item_quantity,0)) AS quantity
FROM item LEFT JOIN orders ON item.item_id = orders.item_id
GROUP BY category,item.item_id,
)
SELECT
category,
SUM(CASE WHEN quantity < 20 THEN 1 ELSE 0 END) AS 'unit0-20',
SUM(CASE WHEN quantity < 50 AND quantity >= 20 THEN 1 ELSE 0 END) AS 'unit20-50',
SUM(CASE WHEN quantity >= 50 THEN 1 ELSE 0 END) AS 'unit50'
FROM CTE

复制代码

李浩泉

/*
Q4: return top 5 zipcode based on revenue per customer
*/
WITH CTE AS
(
SELECT
zipcode,
DENSE_RANK() OVER (PARTITION BY zipcode ORDER BY SUM(ISNULL(item_quantity,0)*item_price) DESC) AS RK
FROM customer LEFT JOIN orders ON customer.customer_id = orders.customer_id
GROUP BY zipcode
)
SELECT zipcode FROM CTE WHERE RK <= 5

复制代码

李浩泉

本帖最后由李浩泉于 2020-9-1 00:02 编辑

/*
Q5: return customer_id whose first order value > second order value
*/
WITH CTE AS
(
SELECT
customer_id,
SUM(ISNULL(item_quantity,0)*item_price) AS value,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY SUM(ISNULL(item_quantity,0)*item_price) DESC) AS RK
FROM orders
GROUP BY customer_id
)
SELECT
customer_id
FROM (
SELECT
customer_id,
CASE WHEN RK = 1 THEN AVG(value) ELSE 0 END AS first_order_value,
CASE WHEN RK = 2 THEN AVG(value) ELSE 0 END AS second_order_value
FROM CTE
GROUP BY customer_id
) T
WHERE first_order_value > second_order_value

复制代码

Q6. Tableau: data blending和data joining的区别

I do not use data blending in Tableau at all. The best practise is to make T-SQL and verify all joins out of the tableau. Once the data output is confirmed correct, deploy the T-SQL directly into the tableau. The data blending is a default function to allow users to merge data when the joins does not exist or hard to be made. The performance of data blending is super slow and not recomended when the data size is large.

	132垅无效楼层，该帖已经被删除

[Leetcode] LC Python 刷题笔记 有志者事竟成

[Leetcode] LC Python 刷题笔记有志者事竟成