Skip to content

Commit d7df7ec

Browse files
mailrunmailrun
authored
Add files via upload
1 parent a9555bc commit d7df7ec

File tree

2 files changed

+249
-0
lines changed

2 files changed

+249
-0
lines changed

‎complexExamples_en.md‎

Lines changed: 161 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,161 @@
1+
## product analysis
2+
### Query the top 10 products with the highest sales volume
3+
```sql
4+
SELECT p.prod_name, SUM(s.quantity_sold) AS total_quantity
5+
FROM sh.sales s
6+
JOIN sh.products p ON s.prod_id = p.prod_id
7+
GROUP BY p.prod_name
8+
ORDER BY total_quantity DESC
9+
FETCH FIRST 10 ROWS ONLY;
10+
```
11+
12+
## Customer Analysis
13+
### Query the total consumption amount of each customer
14+
```sql
15+
SELECT c.cust_last_name, c.cust_first_name, SUM(s.amount_sold) AS total_spent
16+
FROM sh.customers c
17+
JOIN sh.sales s ON c.cust_id = s.cust_id
18+
GROUP BY c.cust_last_name, c.cust_first_name
19+
ORDER BY total_spent DESC;
20+
```
21+
### Query the geographical distribution of customers
22+
```sql
23+
SELECT c.country_id, COUNT(*) AS num_customers
24+
FROM sh.customers c
25+
GROUP BY c.country_id
26+
ORDER BY num_customers DESC;
27+
```
28+
29+
## Channel Analysis
30+
### Query the average order amount for each channel
31+
```sql
32+
SELECT ch.channel_desc, AVG(s.amount_sold) AS avg_order_amount
33+
FROM sh.sales s
34+
JOIN sh.channels ch ON s.channel_id = ch.channel_id
35+
GROUP BY ch.channel_desc
36+
ORDER BY avg_order_amount DESC;
37+
```
38+
### Query the order quantity of each channel
39+
```sql
40+
SELECT ch.channel_desc, COUNT(s.amount_sold) AS num_orders
41+
FROM sh.sales s
42+
JOIN sh.channels ch ON s.channel_id = ch.channel_id
43+
GROUP BY ch.channel_desc
44+
ORDER BY num_orders DESC;
45+
```
46+
47+
## Promotion Analysis
48+
### Query the total sales for each promotion
49+
```sql
50+
SELECT p.promo_name, SUM(s.amount_sold) AS total_sales
51+
FROM sh.sales s
52+
JOIN sh.promotions p ON s.promo_id = p.promo_id
53+
GROUP BY p.promo_name
54+
ORDER BY total_sales DESC;
55+
```
56+
### Check the sales of products participating in a specific promotion (TV program sponsorship)
57+
```sql
58+
SELECT p.prod_name, SUM(s.amount_sold) AS total_sales
59+
FROM sh.sales s
60+
JOIN sh.products p ON s.prod_id = p.prod_id
61+
WHERE s.promo_id IN (SELECT promo_id FROM sh.promotions WHERE promo_subcategory = 'TV program sponsorship')
62+
GROUP BY p.prod_name
63+
ORDER BY total_sales DESC;
64+
```
65+
66+
## Time analysis
67+
### Query the sales volume for each month
68+
```sql
69+
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS total_sales
70+
FROM sh.sales s
71+
JOIN sh.times t ON s.time_id = t.time_id
72+
GROUP BY t.calendar_month_desc
73+
ORDER BY t.calendar_month_desc;
74+
```
75+
76+
## Year-over-year (YoY) growth query
77+
### Query annual sales and its year-on-year growth rate
78+
```sql
79+
SELECT t.calendar_year,
80+
SUM(s.amount_sold) AS total_sales,
81+
LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_year) AS prev_year_sales,
82+
ROUND((SUM(s.amount_sold) - LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_year)) / LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_year) * 100, 2) AS yoy_growth_rate
83+
FROM sh.sales s
84+
JOIN sh.times t ON s.time_id = t.time_id
85+
GROUP BY t.calendar_year
86+
ORDER BY t.calendar_year;
87+
```
88+
89+
## Month-on-month (quarter-on-quarter growth) query
90+
### Query the sales volume and month-on-month growth rate for each quarter
91+
```sql
92+
SELECT t.calendar_quarter_desc,
93+
SUM(s.amount_sold) AS total_sales,
94+
LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_quarter_desc) AS prev_quarter_sales,
95+
ROUND((SUM(s.amount_sold) - LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_quarter_desc)) / LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_quarter_desc) * 100, 2) AS qoq_growth_rate
96+
FROM sh.sales s
97+
JOIN sh.times t ON s.time_id = t.time_id
98+
GROUP BY t.calendar_quarter_desc
99+
ORDER BY t.calendar_quarter_desc;
100+
```
101+
102+
### Query the sales volume and month-on-month growth rate of each month
103+
```sql
104+
SELECT t.calendar_month_desc,
105+
SUM(s.amount_sold) AS total_sales,
106+
LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_month_desc) AS prev_month_sales,
107+
ROUND((SUM(s.amount_sold) - LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_month_desc)) / LAG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_month_desc) * 100, 2) AS mom_growth_rate
108+
FROM sh.sales s
109+
JOIN sh.times t ON s.time_id = t.time_id
110+
GROUP BY t.calendar_month_desc
111+
ORDER BY t.calendar_month_desc;
112+
```
113+
114+
## Comprehensive Example
115+
### Check the impact of price, promotion and channel on sales
116+
```sql
117+
SELECT p.prod_name, ch.channel_desc, pr.promo_name, SUM(s.amount_sold) AS total_sales
118+
FROM sh.sales s
119+
JOIN sh.products p ON s.prod_id = p.prod_id
120+
JOIN sh.channels ch ON s.channel_id = ch.channel_id
121+
JOIN sh.promotions pr ON s.promo_id = pr.promo_id
122+
GROUP BY p.prod_name, ch.channel_desc, pr.promo_name
123+
ORDER BY total_sales DESC;
124+
```
125+
126+
### Query the top 10 products with the highest sales and their year-on-year growth
127+
```sql
128+
WITH product_sales AS (
129+
SELECT p.prod_name, t.calendar_year, SUM(s.amount_sold) AS total_sales
130+
FROM sh.sales s
131+
JOIN sh.products p ON s.prod_id = p.prod_id
132+
JOIN sh.times t ON s.time_id = t.time_id
133+
GROUP BY p.prod_name, t.calendar_year
134+
)
135+
SELECT prod_name, calendar_year, total_sales,
136+
LAG(total_sales) OVER (PARTITION BY prod_name ORDER BY calendar_year) AS prev_year_sales,
137+
ROUND((total_sales - LAG(total_sales) OVER (PARTITION BY prod_name ORDER BY calendar_year)) / LAG(total_sales) OVER (PARTITION BY prod_name ORDER BY calendar_year) * 100, 2) AS yoy_growth_rate
138+
FROM product_sales
139+
WHERE prod_name IN (
140+
SELECT prod_name
141+
FROM product_sales
142+
WHERE calendar_year = (SELECT MAX(calendar_year) FROM product_sales)
143+
ORDER BY total_sales DESC
144+
FETCH FIRST 10 ROWS ONLY
145+
)
146+
ORDER BY prod_name, calendar_year;
147+
```
148+
149+
### Query the quarterly sales and month-on-month growth of each channel
150+
```sql
151+
SELECT ch.channel_desc, t.calendar_quarter_desc,
152+
SUM(s.amount_sold) AS total_sales,
153+
LAG(SUM(s.amount_sold)) OVER (PARTITION BY ch.channel_desc ORDER BY t.calendar_quarter_desc) AS prev_quarter_sales,
154+
ROUND((SUM(s.amount_sold) - LAG(SUM(s.amount_sold)) OVER (PARTITION BY ch.channel_desc ORDER BY t.calendar_quarter_desc)) / LAG(SUM(s.amount_sold)) OVER (PARTITION BY ch.channel_desc ORDER BY t.calendar_quarter_desc) * 100, 2) AS qoq_growth_rate
155+
FROM sh.sales s
156+
JOIN sh.channels ch ON s.channel_id = ch.channel_id
157+
JOIN sh.times t ON s.time_id = t.time_id
158+
GROUP BY ch.channel_desc, t.calendar_quarter_desc
159+
ORDER BY ch.channel_desc, t.calendar_quarter_desc;
160+
```
161+
```

‎sampleSQL_en.md‎

Lines changed: 88 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,88 @@
1+
# Simple SQL Example
2+
3+
## SUM
4+
5+
### 查询所有销售的总金额
6+
```sql
7+
SELECT SUM(amount_sold) AS total_sales FROM sh.sales;
8+
```
9+
10+
### Query the total amount of all sales
11+
```sql
12+
SELECT prod_id, SUM(amount_sold) AS total_sales FROM sh.sales GROUP BY prod_id;
13+
```
14+
15+
## AVG
16+
17+
### Query the average amount of all sales
18+
```sql
19+
SELECT AVG(amount_sold) AS average_sales FROM sh.sales;
20+
```
21+
22+
### Query the average sales amount of each product
23+
```sql
24+
SELECT prod_id, AVG(amount_sold) AS average_sales FROM sh.sales GROUP BY prod_id;
25+
```
26+
27+
## MEDIAN
28+
29+
### Query the median amount of all sales
30+
```sql
31+
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount_sold) AS median_sales FROM sh.sales;
32+
```
33+
34+
### Query the median sales amount of each product
35+
```sql
36+
SELECT prod_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount_sold) AS median_sales FROM sh.sales GROUP BY prod_id;
37+
```
38+
39+
## MAX
40+
41+
### Query the maximum amount of all sales
42+
```sql
43+
SELECT MAX(amount_sold) AS max_sales FROM sh.sales;
44+
```
45+
46+
### Query the maximum amount of all product
47+
```sql
48+
SELECT prod_id, MAX(amount_sold) AS max_sales FROM sh.sales GROUP BY prod_id;
49+
```
50+
51+
## MIN
52+
53+
### Query the minimum amount of all sales
54+
```sql
55+
SELECT MIN(amount_sold) AS min_sales FROM sh.sales;
56+
```
57+
58+
### Query the minimum sales amount for each product
59+
```sql
60+
SELECT prod_id, MIN(amount_sold) AS min_sales FROM sh.sales GROUP BY prod_id;
61+
```
62+
63+
## COUNT
64+
65+
### Query the total number of sales records
66+
```sql
67+
SELECT COUNT(*) AS total_sales_count FROM sh.sales;
68+
```
69+
70+
### Query the sales record quantity of each product
71+
```sql
72+
SELECT prod_id, COUNT(*) AS sales_count FROM sh.sales GROUP BY prod_id;
73+
```
74+
75+
## Comprehensive Example
76+
77+
### Query the total sales amount, average sales amount, maximum sales amount, minimum sales amount and number of sales records for each product
78+
```sql
79+
SELECT prod_id,
80+
SUM(amount_sold) AS total_sales,
81+
AVG(amount_sold) AS average_sales,
82+
MAX(amount_sold) AS max_sales,
83+
MIN(amount_sold) AS min_sales,
84+
COUNT(*) AS sales_count
85+
FROM sh.sales
86+
GROUP BY prod_id;
87+
```
88+
```

0 commit comments

Comments
 (0)