Customer summary report

[davidcantor]

Customer level table:
Customer ID, revenue in 2015, revenue in 2016, has he ever purchased product A (yes / no), has his total revenue to date been higher than 1,000 (yes / no), how many invoices does he have to date

Any ideas how to create this SQL query?

[HansV]

Could you attach a small zipped sample database?

[davidcantor]

ID customerID InvoiceID ProductID Date Income
1 1 1234551 A 1/1/2015 300
2 1 1234552 A 1/2/2016 300
3 2 1234553 B 1/3/2016 500
4 3 1234554 C 1/4/2016 400
5 4 1234555 A 1/5/2015 300
6 4 1234555 B 1/5/2015 500
7 3 1234554 C 1/4/2016 400
8 3 1234556 A 1/8/2016 300
9 3 1234556 B 1/8/2016 500
10 2 1234553 A 1/3/2016 300
11 3 1234557 C 1/11/2016 400
12 4 1234561 D 1/12/2016 1200
13 5 1234565 S 1/13/2016 1800
14 6 1234569 A 1/14/2016 300
15 7 1234573 B 1/15/2016 500
16 8 1234577 C 1/16/2016 400
17 9 1234581 A 1/17/2016 300
18 10 1234585 C 1/18/2016 400
19 11 1234589 B 1/19/2015 500
20 12 1234593 C 1/20/2016 400
21 13 1234597 D 1/21/2016 1200
22 14 1234601 G 1/22/2016 700
23 15 1234605 A 2/23/2016 300
24 16 1234609 D 2/24/2015 1200
25 17 1234613 A 2/25/2016 300
26 18 1234617 C 2/26/2016 400
27 19 1234621 D 2/27/2016 1200
28 20 1234625 A 3/28/2015 300
29 21 1234629 C 3/29/2016 400
30 22 1234633 D 3/30/2016 1200
31 23 1234637 B 3/31/2016 500
32 24 1234641 C 4/1/2016 400

[HansV]

Like with your previous question, you'll have to create separate queries for each of the questions.

[davidcantor]

thank you!