LHi2uPXy

1--品牌销售额占比
2select b.brand_desc,
3       sum(item_spend) as spend
4  from wfj.cust_spend_info_total_items_partition a
5  join wfj.brand b
6    on a.brand_id = b.brand_id 
7 where a.week_id > 201722
8 group by b.brand_desc
9
10--品类销售额new
11select b.category_desc1||'-'||b.category_desc2 as category_desc,
12       sum(item_spend) as category_spend,
13       count(distinct a.brand_id) as brand_num
14  from wfj.cust_spend_info_total_items_partition a
15  join wfj.category b 
16    on a.category_id = b.category_level3
17 where a.week_id > 201721
18 group by 1
19
20
21--品类受欢迎程度
22select e.category_desc1||'-'||e.category_desc2 as category_desc,
23       sum(item_spend) as total_spend,
24       sum(case when a.cust_id > 0  then item_spend else 0 end) as member_spend,
25       sum(case when c.shopping_habit in ('PR','VL') then item_spend else 0 end) as loyal_spend,
26       sum(case when d.price_sensitive_level = 'high' then item_spend else 0 end) as high_spend,
27       count(distinct a.cust_id) as cust_num,
28       sum(item_spend)/count(distinct bask_id) as bask_spend,
29       count(distinct case when c.shopping_habit in ('PR','VL') then a.cust_id else 0 end) as loyal_cust_num,
30       count(distinct case when d.price_sensitive_level = 'high' then a.cust_id else 0 end) as high_cust_num
31  from wfj.cust_spend_info_total_items_partition a 
32  join wfj.category e 
33    on a.category_id = e.category_level3
34  left join wfj.cust_retailer_shopping_habit c 
35    on a.cust_id = c.cust_id
36  left join analyst.wfj_cust_retailer_price_sensitivity d 
37    on a.cust_id =  d.cust_id
38 where a.week_id > 201721
39 group by 1
40
41
42
43--品牌受欢迎程度
44select e.category_desc1||'-'||e.category_desc2 as category_desc,
45       brand_desc,
46       sum(item_spend) as total_spend,
47       sum(case when a.cust_id > 0  then item_spend else 0 end) as member_spend,
48       sum(case when c.shopping_habit in ('PR','VL') then item_spend else 0 end) as loyal_spend,
49       sum(case when d.price_sensitive_level = 'high' then item_spend else 0 end) as high_spend,
50       count(distinct a.cust_id) as cust_num,
51       sum(item_spend)/count(distinct bask_id) as bask_spend,
52       count(distinct case when c.shopping_habit in ('PR','VL') then a.cust_id else 0 end) as loyal_cust_num,
53       count(distinct case when d.price_sensitive_level = 'high' then a.cust_id else 0 end) as high_cust_num
54  from wfj.cust_spend_info_total_items_partition a 
55  join wfj.brand b
56    on a.brand_id = b.brand_id
57  join wfj.category e 
58    on a.category_id = e.category_level3
59  left join wfj.cust_retailer_shopping_habit c 
60    on a.cust_id = c.cust_id
61  left join analyst.wfj_cust_retailer_price_sensitivity d 
62    on a.cust_id =  d.cust_id
63 where a.week_id > 201721
64 group by 1,2
65
66--不同品牌的品类渗透率
67select m.category_desc,
68       m.brand_desc,
69       b_cust_num/c_cust_num::numeric as cate_pene,
70       b_loyal_cust_num/c_loyal_cust_num::numeric as cate_loyal_pene,
71       b_high_cust_num/c_high_cust_num::numeric as cate_high_pene
72from
73(select e.category_desc1||'-'||e.category_desc2 as category_desc,
74       brand_desc,
75       count(distinct a.cust_id) as b_cust_num,
76       count(distinct case when c.shopping_habit in ('PR','VL') then a.cust_id else 0 end) as b_loyal_cust_num,
77       count(distinct case when d.price_sensitive_level = 'high' then a.cust_id else 0 end) as b_high_cust_num
78  from wfj.cust_spend_info_total_items_partition a 
79  join wfj.brand b
80    on a.brand_id = b.brand_id
81  join wfj.category e
82    on a.category_id = e.category_level3
83  left join wfj.cust_retailer_shopping_habit c 
84    on a.cust_id = c.cust_id
85  left join wfj.cust_retailer_price_sensitivity d 
86    on a.cust_id =  d.cust_id
87 where a.week_id > 201721
88 group by 1,2)m
89 join 
90 (select b.category_desc1||'-'||b.category_desc2 as category_desc,
91       count(distinct a.cust_id) as c_cust_num,
92       count(distinct case when c.shopping_habit in ('PR','VL') then a.cust_id else 0 end) as c_loyal_cust_num,
93       count(distinct case when d.price_sensitive_level = 'high' then a.cust_id else 0 end) as c_high_cust_num
94  from wfj.cust_spend_info_total_items_partition a 
95  join wfj.category b
96    on a.category_id = b.category_level3
97  left join wfj.cust_retailer_shopping_habit c 
98    on a.cust_id = c.cust_id
99  left join wfj.cust_retailer_price_sensitivity d 
100    on a.cust_id =  d.cust_id
101 where a.week_id > 201721
102 group by 1)n 
103 on m.category_desc = n.category_desc
104
105
106--品牌销售额和渗透率的变化
107drop table if exists analyst.wfj_cust_spend_pene_last52wk;
108create table analyst.wfj_cust_spend_pene_last52wk
109as
110select m.category_desc,
111       brand_desc,
112       m.total_spend,
113       avg_prod_spend,
114       b_cust_num/c_cust_num::numeric as cate_pene
115from 
116(select b.category_desc1||'-'||b.category_desc2 as category_desc,
117       brand_desc,
118       sum(item_spend) as total_spend,
119       sum(item_spend)/sum(item_quantity)::numeric as avg_prod_spend,
120       count(distinct cust_id) as b_cust_num 
121  from wfj.cust_spend_info_total_items_partition a
122  join wfj.category b
123    on a.category_id = b.category_level3
124  join wfj.brand c 
125    on a.brand_id = c.brand_id
126 where week_id > 201721
127 group by 1,2 )m 
128join 
129(select b.category_desc1||'-'||b.category_desc2 as category_desc,
130       sum(item_spend) as total_spend,
131       count(distinct cust_id) as c_cust_num 
132  from wfj.cust_spend_info_total_items_partition a
133  join wfj.category b
134    on a.category_id = b.category_level3
135 where week_id > 201721
136 group by 1)n
137on m.category_desc = n.category_desc;
138 
139drop table if exists analyst.wfj_cust_spend_pene_last104wk;
140create table analyst.wfj_cust_spend_pene_last104wk
141as
142select m.category_desc,
143       brand_desc,
144       m.total_spend,
145       avg_prod_spend,
146       b_cust_num/c_cust_num::numeric as cate_pene
147from 
148(select b.category_desc1||'-'||b.category_desc2 as category_desc,
149       brand_desc,
150       sum(item_spend) as total_spend,
151       sum(item_spend)/sum(item_quantity)::numeric as avg_prod_spend,
152       count(distinct cust_id) as b_cust_num 
153  from wfj.cust_spend_info_total_items_partition a
154  join wfj.category b
155    on a.category_id = b.category_level3
156  join wfj.brand c 
157    on a.brand_id = c.brand_id
158 where week_id between 201623 and 201722
159 group by 1,2)m 
160join 
161(select b.category_desc1||'-'||b.category_desc2 as category_desc,
162       sum(item_spend) as total_spend,
163       count(distinct cust_id) as c_cust_num 
164  from wfj.cust_spend_info_total_items_partition a
165  join wfj.category b
166    on a.category_id = b.category_level3
167 where week_id between 201623 and 201722
168 group by 1)n
169on m.category_desc = n.category_desc
170
171select a.category_desc,
172       a.brand_desc,
173       a.total_spend as last52wk_spend,
174       b.total_spend as last104wk_spend,
175       a.total_spend / c.sales_weeks2 as avg_last52wk_spend,
176       b.total_spend / c.sales_weeks1 as avg_last104wk_spend,
177       a.avg_prod_spend as last52wk_prod_spend,
178       b.avg_prod_spend as last104wk_prod_spend,
179       a.cate_pene as last52wk_cate_pene,
180       b.cate_pene as last104wk_cate_pene
181  from analyst.wfj_cust_spend_pene_last52wk a 
182  join analyst.wfj_cust_spend_pene_last104wk b 
183    on (a.category_desc = b.category_desc and a.brand_desc = b.brand_desc)
184  join analyst.wfj_brand_sales_week c 
185    on (a.category_desc = c.category_desc and a.brand_desc = c.brand_desc)
186 where c.sales_weeks1 > 0 and c.sales_weeks2 > 0
187
188--select * from analyst.wfj_brand_sales_week
189
190
191--品类的渗透率和销售额变化
192select m.category_desc,
193       m.cust_num as cust_num_52,
194       n.cust_num as cust_num_104,
195       m.total_spend as spend_52,
196       n.total_spend as spend_104
197from 
198(select b.category_desc1||'-'||b.category_desc2 as category_desc,
199       count(distinct cust_id) as cust_num,
200       sum(item_spend) as total_spend
201  from wfj.cust_spend_info_total_items_partition a 
202  join wfj.category b
203    on a.category_id = b.category_level3
204 where week_id > 201721
205 group by 1)m
206join
207(select b.category_desc1||'-'||b.category_desc2 as category_desc,
208       count(distinct cust_id) as cust_num,
209       sum(item_spend) as total_spend
210  from wfj.cust_spend_info_total_items_partition a 
211  join wfj.category b
212    on a.category_id = b.category_level3
213 where week_id between 201623 and 201722
214 group by 1)n
215on m.category_desc = n.category_desc
216
217
218--品牌关联性
219create table analyst.wfj_cust_universe 
220as
221select a.cust_id,
222       a.brand_id,
223       sum(item_spend)as spend,
224       count(distinct new_bask_id) as bask
225  from analyst.cust_spend_info_total_items_partition_wfj a 
226 where a.week_id > 201722
227   and a.cust_id > 0
228 group by 1,2
229
230
231
232create table analyst.wfj_brand_universe
233as
234SELECT
235  t1.brand_id AS brand_1,
236  t2.brand_id AS brand_2,
237  COUNT(DISTINCT t1.cust_id) AS cust
238FROM
239  analyst.wfj_cust_universe  t1,
240  (
241    SELECT
242      cust_id,
243      brand_id
244    FROM
245      analyst.wfj_cust_universe 
246  ) t2
247WHERE
248  t1.cust_id = t2.cust_id
249GROUP BY
250  1,
251  2;
252
253select DISTINCT a.*,
254       b.brand_desc as brand_desc1,
255       c.brand_desc as brand_desc2,
256       d.category_desc1||'-'||d.category_desc2 as category_desc1,
257       e.category_desc1||'-'||e.category_desc2 as category_desc2
258  from analyst.wfj_brand_universe a 
259  join wfj.brand b 
260    on a.brand_1 = b.brand_id 
261  join wfj.brand c 
262    on a.brand_2 = c.brand_id
263  join wfj.category d 
264    on b.category_id = d.category_level3 
265  join wfj.category e
266    on c.category_id = e.category_level3
267 where a.brand_1 != a.brand_2
268
269select a.brand_id,
270       brand_desc,
271       count(distinct cust_id) as cust
272from analyst.wfj_cust_universe a
273join wfj.brand b 
274  on a.brand_id = b.brand_id
275group by 1,2;
276
277--122970
278select count(distinct cust_id) as cust
279from analyst.wfj_cust_universe
280
281select count(distinct ctm_customer_id) as cust
282from dtm_cn.harb_cust_universe;
283
284
285--品类-忠诚度细分
286select b.category_desc1||'-'||b.category_desc2 as category_desc,
287       sum(case when c.shopping_habit in('PR','VL') then item_spend else 0 end) as loyal_spend,
288       sum(case when c.shopping_habit in('UN','PO') then item_spend else 0 end) as op_spend,
289       sum(case when c.shopping_habit in('LP') then item_spend else 0 end) as loss_spend,
290       sum(case when c.shopping_habit in('NEW') then item_spend else 0 end) as new_spend,
291       sum(case when a.cust_id != 0 then item_spend else 0 end) as member_spend,
292       sum(item_spend) as total_spend,
293       count(distinct case when c.shopping_habit in('PR','VL') then a.cust_id else null end) as loyal_cust_num,
294       count(distinct case when c.shopping_habit in('UN','PO') then a.cust_id else null end) as op_cust_num,
295       count(distinct case when c.shopping_habit in('LP') then a.cust_id else null end) as loss_cust_num,
296       count(distinct case when c.shopping_habit in('NEW') then a.cust_id else null end) as new_cust_num,
297       count(distinct a.cust_id) as member_cust_num
298  from wfj.cust_spend_info_total_items_partition a 
299  join wfj.category b 
300    on a.category_id = b.category_level3
301  left join wfj.cust_retailer_shopping_habit c
302    on a.cust_id = c.cust_id 
303 where a.week_id > 201721
304 group by 1
305
306--品类-价格关注度细分
307select b.category_desc1||'-'||b.category_desc2 as category_desc,
308       sum(case when c.price_sensitive_level = 'high' then item_spend else 0 end) as high_spend,
309       sum(case when c.price_sensitive_level = 'medium' then item_spend else 0 end) as medium_spend,
310       sum(case when c.price_sensitive_level = 'low' then item_spend else 0 end) as low_spend,
311       sum(case when a.cust_id != 0 then item_spend else 0 end) as member_spend,
312       sum(item_spend) as total_spend,
313       count(distinct case when c.price_sensitive_level = 'high' then a.cust_id else null end) as high_cust_num,
314       count(distinct case when c.price_sensitive_level = 'medium' then a.cust_id else null end) as medium_cust_num,
315       count(distinct case when c.price_sensitive_level = 'low' then a.cust_id else null end) as low_cust_num,
316       count(distinct a.cust_id) as member_cust_num
317  from wfj.cust_spend_info_total_items_partition a 
318  join wfj.category b 
319    on a.category_id = b.category_level3
320  left join analyst.wfj_cust_retailer_price_sensitivity c
321    on a.cust_id = c.cust_id 
322 where a.week_id > 201721
323 group by 1
324
325
326--品类价格带及销售占比
327create table analyst.wfj_brand_unit_price
328as
329select a.bask_id,
330       b.category_desc1||'-'||b.category_desc2 as category_desc,
331       c.brand_desc,
332       sum(item_spend)/sum(item_quantity) as avg_price,
333       sum(item_spend) as spend
334 from wfj.cust_spend_info_total_items_partition a 
335 join wfj.category b 
336   on a.category_id = b.category_level3
337 join wfj.brand c 
338   on a.brand_id = c.brand_id
339 where a.week_id > 201721
340 group by 1,2,3
341 having sum(item_spend)>0
342
343
344select category_desc,
345       brand_desc,
346       round(avg_price/500)*500 as price_band,
347       sum(spend) as price_spend
348  from analyst.wfj_brand_unit_price
349 where category_desc like '%名品-女装%' 
350   and avg_price <15000
351 group by 1,2,3
352 order by 1,2,3
353
354--前一年品类价格带及销售占比
355create table analyst.wfj_brand_unit_price_year1
356as
357select a.bask_id,
358       b.category_desc1||'-'||b.category_desc2 as category_desc,
359       c.brand_desc,
360       sum(item_spend)/sum(item_quantity) as avg_price,
361       sum(item_spend) as spend
362 from wfj.cust_spend_info_total_items_partition a 
363 join wfj.category b 
364   on a.category_id = b.category_level3
365 join wfj.brand c 
366   on a.brand_id = c.brand_id
367 where a.week_id between 201622 and 201721
368 group by 1,2,3
369 having sum(item_spend)>0
370
371
372select category_desc,
373       brand_desc,
374       round(avg_price/500)*500 as price_band,
375       sum(spend) as price_spend
376  from analyst.wfj_brand_unit_price_year1
377 where category_desc like '%名品-女装%' 
378   and avg_price <10000
379 group by 1,2,3
380 order by 1,2,3
381
382
383select analyst.cust_loyalty_seg_new('wfj',201721)
384--忠诚顾客来客数下降最快的品牌
385select DISTINCT m.category_desc,
386       m.brand_desc,
387       m.total_spend as spend2018,
388       n.total_spend as spend2017,
389       m.avg_prod_spend as prod_spend2018,
390       n.avg_prod_spend as prod_spend2017,
391       m.cust_num as cust_num2018,
392       n.cust_num as cust_num2017
393from 
394(select b.category_desc1||'-'||b.category_desc2 as category_desc,
395       brand_desc,
396       sum(item_spend) as total_spend,
397       sum(item_spend)/sum(item_quantity)::numeric as avg_prod_spend,
398       count(distinct a.cust_id) as cust_num 
399  from wfj.cust_spend_info_total_items_partition a
400  join wfj.category b
401    on a.category_id = b.category_level3
402  join wfj.brand c 
403    on a.brand_id = c.brand_id
404  join wfj.cust_retailer_shopping_habit d 
405    on a.cust_id = d.cust_id
406 where a.week_id between 201722 and 201821
407 group by 1,2)m
408join
409(select b.category_desc1||'-'||b.category_desc2 as category_desc,
410       brand_desc,
411       sum(item_spend) as total_spend,
412       sum(item_spend)/sum(item_quantity)::numeric as avg_prod_spend,
413       count(distinct a.cust_id) as cust_num 
414  from wfj.cust_spend_info_total_items_partition a
415  join wfj.category b
416    on a.category_id = b.category_level3
417  join wfj.brand c 
418    on a.brand_id = c.brand_id
419  join analyst.wfj_cust_loyalty_new_201721 d 
420    on a.cust_id = d.cust_id
421 where a.week_id between 201622 and 201721
422 group by 1,2)n
423on (m.category_desc = n.category_desc and m.brand_desc = n.brand_desc)
424
425
426--品质顾客品单价下降最快的品牌
427select DISTINCT m.category_desc,
428       m.brand_desc,
429       m.total_spend as spend2018,
430       n.total_spend as spend2017,
431       m.avg_prod_spend as prod_spend2018,
432       n.avg_prod_spend as prod_spend2017,
433       m.cust_num as cust_num2018,
434       n.cust_num as cust_num2017
435from 
436(select b.category_desc1||'-'||b.category_desc2 as category_desc,
437       brand_desc,
438       sum(item_spend) as total_spend,
439       sum(item_spend)/sum(item_quantity)::numeric as avg_prod_spend,
440       count(distinct a.cust_id) as cust_num 
441  from wfj.cust_spend_info_total_items_partition a
442  join wfj.category b
443    on a.category_id = b.category_level3
444  join wfj.brand c 
445    on a.brand_id = c.brand_id
446  join wfj.cust_retailer_price_sensitivity d 
447    on a.cust_id = d.cust_id
448 where a.week_id between 201722 and 201821
449 group by 1,2)m
450join
451(select b.category_desc1||'-'||b.category_desc2 as category_desc,
452       brand_desc,
453       sum(item_spend) as total_spend,
454       sum(item_spend)/sum(item_quantity)::numeric as avg_prod_spend,
455       count(distinct a.cust_id) as cust_num 
456  from wfj.cust_spend_info_total_items_partition a
457  join wfj.category b
458    on a.category_id = b.category_level3
459  join wfj.brand c 
460    on a.brand_id = c.brand_id
461  join wfj.cust_retailer_price_sensitivity d 
462    on a.cust_id = d.cust_id
463 where a.week_id between 201622 and 201721
464 group by 1,2)n
465on (m.category_desc = n.category_desc and m.brand_desc = n.brand_desc)
466
467
468--销售额、来客数、品单价的变化
469select DISTINCT m.category_desc,
470       m.brand_desc,
471       m.total_spend as spend2018,
472       n.total_spend as spend2017,
473       m.avg_prod_spend as prod_spend2018,
474       n.avg_prod_spend as prod_spend2017,
475       m.cust_num as cust_num2018,
476       n.cust_num as cust_num2017,
477       k.error
478from 
479(select b.category_desc1||'-'||b.category_desc2 as category_desc,
480       a.brand_id,
481       brand_desc,
482       sum(item_spend) as total_spend,
483       sum(item_spend)/sum(item_quantity)::numeric as avg_prod_spend,
484       count(distinct a.bask_id) as cust_num 
485  from wfj.cust_spend_info_total_items_partition a
486  join wfj.category b
487    on a.category_id = b.category_level3
488  join wfj.brand c 
489    on a.brand_id = c.brand_id
490 where a.week_id between 201722 and 201821
491 group by 1,2,3)m
492join
493(select b.category_desc1||'-'||b.category_desc2 as category_desc,
494       a.brand_id,
495       brand_desc,
496       sum(item_spend) as total_spend,
497       sum(item_spend)/sum(item_quantity)::numeric as avg_prod_spend,
498       count(distinct a.bask_id) as cust_num 
499  from wfj.cust_spend_info_total_items_partition a
500  join wfj.category b
501    on a.category_id = b.category_level3
502  join wfj.brand c 
503    on a.brand_id = c.brand_id
504 where a.week_id between 201622 and 201721
505 group by 1,2,3)n
506on (m.category_desc = n.category_desc and m.brand_desc = n.brand_desc)
507left join analyst.wfj_brand_min_max_shopdate k 
508  on m.brand_id = k.brand_id
509
510
511drop table if exists analyst.wfj_brand_sales_week;
512create table analyst.wfj_brand_sales_week
513as
514select a.*,
515       c.category_desc1||'-'||c.category_desc2 as category_desc,
516       case when 201652-first_purchase_week >= 0 then (201652-first_purchase_week+22)
517            when 201721 -  first_purchase_week >= 0 then (201722 - first_purchase_week) 
518            else 0 end as sales_weeks1,
519       case when last_purchase_week - 201801 >= 0 and first_purchase_week <= 201722 then (last_purchase_week - 201800 + 31)
520            when last_purchase_week - 201801 >= 0 and first_purchase_week >= 201801 then (last_purchase_week - first_purchase_week +1)
521            when last_purchase_week - 201801 >= 0 and first_purchase_week > 201722 then (last_purchase_week - 201800 + 201753-first_purchase_week)
522            when last_purchase_week - 201801 < 0 and  last_purchase_week >= 201722 and first_purchase_week <= 201722 then (last_purchase_week - 201721)
523            when last_purchase_week - 201801 < 0 and first_purchase_week > 201722 then (last_purchase_week - first_purchase_week + 1)
524            else 0 end as sales_weeks2
525  from analyst.wfj_brand_min_max_shopdate a
526  join wfj.brand b 
527    on a.brand_id = b.brand_id
528  join wfj.category c 
529    on b.category_id = c.category_level3 
530
531--新客最喜欢的品牌
532select a.brand_id,
533       brand_desc,
534       count(distinct a.cust_id) as cust_num
535  from wfj.cust_spend_info_total_items_partition a 
536  join wfj.cust_retailer_shopping_habit b 
537    on a.cust_id = b.cust_id
538  join wfj.brand c 
539    on a.brand_id = c.brand_id
540 where b.shopping_habit = 'NEW'
541   and a.week_id between 201722 and 201821
542 group by 1,2
543 order by 3 desc
544
545
546
547--品类关联性
548create table analyst.wfj_cust_category_universe 
549as
550select a.cust_id,
551       b.category_desc1||'-'||b.category_desc2 as category_desc,
552       sum(item_spend)as spend,
553       count(distinct new_bask_id) as bask
554  from analyst.cust_spend_info_total_items_partition_wfj a 
555  join wfj.category b 
556    on a.category_id = b.category_level3
557 where a.week_id > 201722
558   and a.cust_id > 0
559 group by 1,2
560
561
562
563create table analyst.wfj_category_universe
564as
565SELECT
566  t1.category_desc AS category_desc_1,
567  t2.category_desc AS category_desc_2,
568  COUNT(DISTINCT t1.cust_id) AS cust
569FROM
570  analyst.wfj_cust_category_universe  t1,
571  (
572    SELECT
573      cust_id,
574      category_desc
575    FROM
576      analyst.wfj_cust_category_universe
577  ) t2
578WHERE
579  t1.cust_id = t2.cust_id
580GROUP BY
581  1,
582  2;
583
584select * from analyst.wfj_category_universe 
585
586select 
587       a.category_desc,
588       count(distinct cust_id) as cust
589from analyst.wfj_cust_category_universe a
590
591group by 1;
592
593--122970
594select count(distinct cust_id) as cust
595from analyst.wfj_cust_universe
596
597select count(distinct ctm_customer_id) as cust
598from dtm_cn.harb_cust_universe;
599
600
601
602select a.brand_id,
603       b.brand_desc,
604       sum(case when a.week_id between 201622 and 201721 then item_spend else 0 end) as spend2017,
605       sum(case when a.week_id between 201722 and 201821 then item_spend else 0 end) as spend2018
606  from wfj.cust_spend_info_total_items_partition a 
607  join wfj.brand b 
608    on a.brand_id = b.brand_id 
609  join wfj.category c 
610    on a.category_id = c.category_level3 
611 where c.category_level2 = 100300000000009
612 group by 1,2
613
614
615
616--歌中歌品牌顾客流失分析
617--前一年顾客
618drop table if exists analyst.wfj_cust_gzg_2017;
619create table analyst.wfj_cust_gzg_2017
620as
621select distinct cust_id 
622  from wfj.cust_spend_info_total_items_partition 
623 where week_id between 201622 and 201721
624   and brand_id = 100300000000250
625   and cust_id !=0;
626
627drop table if exists analyst.wfj_cust_gzg_2018;
628create table analyst.wfj_cust_gzg_2018
629as
630select distinct cust_id 
631  from wfj.cust_spend_info_total_items_partition 
632 where week_id between 201722 and 201821
633   and brand_id = 100300000000250
634   and cust_id !=0;
635
636
637--260
638select count(distinct a.cust_id) 
639  from analyst.wfj_cust_gzg_2017 a 
640  join analyst.wfj_cust_gzg_2018 b 
641    on a.cust_id = b.cust_id;
642
643--2339
644select count(distinct cust_id) from analyst.wfj_cust_gzg_2017;
645
646--2749
647select count(distinct cust_id) from analyst.wfj_cust_gzg_2018;
648
649create table analyst.wfj_cust_gzg_loss
650as
651select distinct a.cust_id
652  from analyst.wfj_cust_gzg_2017 a 
653 where a.cust_id not in (select distinct b.cust_id from analyst.wfj_cust_gzg_2018 b)
654
655--2079
656select count(distinct cust_id) from analyst.wfj_cust_gzg_loss;
657
658--734
659select count(distinct a.cust_id) 
660  from analyst.wfj_cust_gzg_loss a 
661 where a.cust_id not in (select distinct b.cust_id from wfj.cust_spend_info_total_items_partition b where b.week_id between 201722 and 201821)
662
663--451
664select count(distinct a.cust_id) 
665  from analyst.wfj_cust_gzg_loss a 
666 where a.cust_id  in (select distinct b.cust_id from wfj.cust_spend_info_total_items_partition b where b.week_id between 201722 and 201821)
667   and a.cust_id not in (select distinct b.cust_id from wfj.cust_spend_info_total_items_partition b join wfj.category c on b.category_id = c.category_level3 where c.category_level2 =100300000000009 and b.week_id between 201722 and 201821)
668
669create table analyst.wfj_cust_gzg_loss_brand
670as
671select distinct a.cust_id
672  from analyst.wfj_cust_gzg_loss a 
673 where a.cust_id  in (select distinct b.cust_id from wfj.cust_spend_info_total_items_partition b where b.week_id between 201722 and 201821)
674   and a.cust_id  in (select distinct b.cust_id from wfj.cust_spend_info_total_items_partition b join wfj.category c on b.category_id = c.category_level3 where c.category_level2 =100300000000009 and b.week_id between 201722 and 201821)
675   and a.cust_id not in (select distinct b.cust_id from wfj.cust_spend_info_total_items_partition b where brand_id = 100300000000250 and b.week_id between 201722 and 201821)
676
677--894
678select count(distinct a.cust_id) 
679  from analyst.wfj_cust_gzg_loss_brand a
680
681select c.brand_desc,
682       count(distinct a.cust_id) as cust_num
683  from analyst.wfj_cust_gzg_loss_brand a 
684  join wfj.cust_spend_info_total_items_partition b 
685    on a.cust_id = b.cust_id 
686  join wfj.brand c
687    on b.brand_id = c.brand_id
688  join wfj.category d 
689    on b.category_id = d.category_level3 
690 where b.week_id between 201722 and 201821
691   and d.category_level2 =100300000000009
692 group by 1
693 order by 2 desc
694
695
696----------------------------
697--125351会员
698select count(distinct cust_id) from wfj.cust_spend_info_total_items_partition where week_id > 201721
699
700--32197会员
701select count(distinct cust_id) from wfj.cust_spend_info_total_items_partition where week_id between 201622 and 201721
702
703--17766忠诚会员
704select count(distinct cust_id) from wfj.cust_retailer_shopping_habit where shopping_habit in ('PR','VL')
705
706select * from wfj.cust_retailer_shopping_habit
707
708--6533品质会员
709select count(distinct cust_id) from wfj.cust_retailer_price_sensitivity where price_sensitive_level = 'high'
710
711select distinct category_desc3 from wfj.category
712
713select * from wfj.brand where brand_desc = '宝姿'
714
715select * from analyst.wfj_cust_spend_info where cust_id =0 limit 100 where brand_id = 100300000000110;
716
717select count(distinct cust_id) from wfj.cust_spend_info_total_items_partition where category_id = 100300000000003
718
719select distinct a.brand_id,b.category_desc1||'-'||b.category_desc2 as category_desc from wfj.brand a join wfj.category b on a.category_id = b.category_level3
720
721select * from analyst.wfj_brand_sales_week where category_desc = '名品-女装'order by brand_id desc
722
723select * from analyst.wfj_brand_min_max_shopdate order by last_purchase_week desc
724
725
726select * from wfj.product_city