· 8 years ago · Mar 04, 2018, 06:12 AM
1-- want to get counts of people who's last name starts with a vowel (AEIOU)
2-- case, substring
3
4-- SUM(CASE WHEN x.thing = 'whatever' THEN 1 ELSE 0 END) as counts_of_whatever
5
6SELECT t.my_case_outcome, count(*) FROM (
7SELECT c.*, substring(c.last_name, '^[AEIOUaeiou]') as x,
8
9CASE
10 WHEN substring(c.last_name, '^[AEIOUaeiou]') IS NOT NULL THEN 'last_starts_vow'
11 ELSE 'novowel'
12 END as my_case_outcome,
13
14CASE
15 WHEN substring(c.last_name, '^[AEIOUaeiou]') IS NOT NULL THEN substring(c.last_name, '^[AEIOUaeiou]')
16 ELSE 'novowel'
17 END as the_letter_or_not
18
19FROM customer c
20)t
21
22GROUP BY 1
23
24
25----
26
27SELECT t.*,
28 t.payment_date - t.prior_order as some_interval, -- raw interval
29 EXTRACT(epoch FROM t.payment_date - t.prior_order ) / 3600 as hours_since-- interval to hours
30
31 FROM (
32 SELECT p.*,
33 lag(p.payment_date) OVER (PARTITION BY p.customer_id) as prior_order
34 FROM payment p
35)t
36
37-- Alternate Syntax and Some Moving Calculations
38
39SELECT p.* ,
40 avg(p.amount) over w2 as avg_over_prior7,
41 avg(p.amount) over w2 as back3_fwd_3_avg
42FROM payment p
43
44WINDOW w AS (PARTITION BY p.customer_id ORDER BY p.payment_id ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING),
45 w2 AS (PARTITION BY p.customer_id ROWS BETWEEN 7 PRECEDING AND 0 FOLLOWING)
46
47
48
49
50-----
51
52-- CREATE TABLE IF NOT EXISTS customer_sources (
53-- customer_id integer REFERENCES customer(customer_id) ON DELETE RESTRICT,
54-- traffic_source text,
55-- PRIMARY KEY(customer_id)
56-- );
57
58SELECT c.customer_id, c.first_name, c.email, cs.*
59FROM customer c JOIN customer_sources cs ON cs.customer_id = c.customer_id
60
61-- Source / medium, cost, month
62
63DROP TABLE source_spend_all;
64
65CREATE TABLE IF NOT EXISTS source_spend_all (
66 spend_source text,
67 spend integer,
68 visits integer
69);
70
71SELECT t.spend_source, max(t.spend)::money as spend,
72 count(*) as customers,
73 (max(t.spend)/count(*))::money as CPA,
74 (SUM(t.LTV) / 3)::money as total_gross_margin
75
76 FROM (
77 SELECT ssa.*,cs.*, (
78 SELECT sum(p.amount) FROM payment p WHERE cs.customer_id = p.customer_id
79 ) as LTV
80 FROM source_spend_all ssa
81 JOIN customer_sources cs ON cs.traffic_source = ssa.spend_source
82)t
83
84GROUP BY 1 ORDER BY 2 DESC
85
86
87
88----
89
90-- First, there's no right way to do this! Just try to get
91-- data in a format that you can understand, then iterate
92
93
94-- I like breaking it into pieces, buyerid/email, first order, last order, total spend
95
96WITH base_table AS (
97 SELECT p.customer_id, p.payment_date, p.payment_id,
98 row_number() OVER(partition by p.customer_id ORDER BY p.payment_date ASC) as order_rank_early,
99 row_number() OVER(partition by p.customer_id ORDER BY p.payment_date DESC) as order_rank_late,
100 (
101 SELECT SUM(p2.amount) FROM payment p2 WHERE p2.customer_id = p.customer_id
102 ) as LTV
103 FROM payment p
104 GROUP BY 1,2,3
105 ORDER BY 1,2
106), second_table AS (
107
108 SELECT bt.*
109 FROM
110 base_table bt
111 WHERE bt.order_rank_early = 1 OR bt.order_rank_late = 1
112)
113
114SELECT st.customer_id, min(st.payment_date), max(st.payment_date), min(st.ltv)
115FROM second_table st
116GROUP BY 1 ORDER BY 1
117
118
119
120-- still need their top rating as well as all rating rented from (R, PG, etc)
121SELECT r.customer_id, r.inventory_id, i.film_id, f.rating
122FROM rental r -- start with the activity
123 JOIN inventory i on i.inventory_id = r.inventory_id
124 JOIN film f ON f.film_id = i.film_id
125
126
127-- still need their top rating as well as all rating rented from (R, PG, etc)
128-- not worry about ties:
129
130SELECT * FROM (
131SELECT r.customer_id, f.rating, COUNT(*),
132row_number() OVER(partition by r.customer_id ORDER BY COUNT(*) DESC) as rental_freq_rank
133FROM rental r -- start with the activity
134 JOIN inventory i on i.inventory_id = r.inventory_id
135 JOIN film f ON f.film_id = i.film_id
136GROUP BY 1,2
137ORDER BY 1, 3 DESC
138)t WHERE t.rental_freq_rank = 1
139
140
141
142
143SELECT r.customer_id, array_agg(distinct f.rating)
144FROM rental r -- start with the activity
145 JOIN inventory i on i.inventory_id = r.inventory_id
146 JOIN film f ON f.film_id = i.film_id
147GROUP BY 1