Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE public.responses (
  id bigserial PRIMARY KEY
, crm_id int
, question_id int
, response text
, created_at date
, updated_at date
);

INSERT INTO public.responses VALUES
  ('53', '15', '39', 'Yes', '2015-07-17', '2015-07-17')
, ('54', '15', '40', 'Yes', '2015-07-17', '2015-07-17')
, ('55', '15', '41', 'Yes', '2015-07-17', '2015-07-17')
, ('56', '15', '42', 'Yes', '2015-07-17', '2015-07-17')
, ('57', '15', '43', 'Yes', '2015-07-17', '2015-07-17')
, ('58', '15', '44', 'Yes', '2015-07-17', '2015-07-17')
, ('59', '15', '31', 'Yes', '2015-07-17', '2015-07-17')
, ('60', '15', '32', 'Yes', '2015-07-17', '2015-07-17')
, ('61', '15', '33', 'Yes', '2015-07-17', '2015-07-17')
, ('62', '15', '34', 'Yes', '2015-07-17', '2015-07-17')
, ('63', '15', '35', 'Yes', '2015-07-17', '2015-07-17')
, ('64', '15', '36', 'Yes', '2015-07-17', '2015-07-17')
, ('65', '15', '37', 'Yes', '2015-07-17', '2015-07-17')
, ('66', '15', '38', 'Yes', '2015-07-17', '2015-07-17')
, ('67', '16', '39', 'Yes', '2015-07-17', '2015-07-17')
, ('68', '16', '40', 'Yes', '2015-07-17', '2015-07-17')
, ('69', '16', '41', 'Yes', '2015-07-17', '2015-07-17')
, ('70', '16', '42', 'Yes', '2015-07-17', '2015-07-17')
, ('71', '16', '43', 'Yes', '2015-07-17', '2015-07-17')
, ('72', '16', '44', 'Yes', '2015-07-17', '2015-07-17')
, ('73', '16', '31', 'Yes', '2015-07-17', '2015-07-17')
, ('74', '16', '32', 'Yes', '2015-07-17', '2015-07-17')
, ('75', '16', '33', 'Yes', '2015-07-17', '2015-07-17')
, ('76', '16', '34', 'No' , '2015-07-17', '2015-07-17')
, ('77', '16', '35', NULL , '2015-07-17', '2015-07-17')
, ('78', '16', '36', NULL , '2015-07-17', '2015-07-17')
, ('79', '16', '37', NULL , '2015-07-17', '2015-07-17')
, ('80', '16', '38', 'No' , '2015-07-17', '2015-07-17')
, ('81', '17', '39', ''   , '2015-07-17', '2015-07-17')
, ('82', '17', '40', NULL , '2015-07-17', '2015-07-17')
, ('83', '17', '41', NULL , '2015-07-17', '2015-07-17')
, ('84', '17', '42', NULL , '2015-07-17', '2015-07-17')
, ('85', '17', '43', NULL , '2015-07-17', '2015-07-17')
, ('86', '17', '44', NULL , '2015-07-17', '2015-07-17')
, ('87', '17', '31', ''   , '2015-07-17', '2015-07-17')
, ('88', '17', '32', NULL , '2015-07-17', '2015-07-17')
, ('89', '17', '33', NULL , '2015-07-17', '2015-07-17')
, ('90', '17', '34', 'Yes', '2015-07-17', '2015-07-17')
, ('91', '17', '35', 'Yes', '2015-07-17', '2015-07-17')
, ('92', '17', '36', 'Yes', '2015-07-17', '2015-07-17')
, ('93', '17', '37', 'Yes', '2015-07-17', '2015-07-17')
, ('94', '17', '38', 'Yes', '2015-07-17', '2015-07-17')
, ('95', '18', '39', ''   , '2015-07-17', '2015-07-17')
, ('96', '18', '40', NULL , '2015-07-17', '2015-07-17')
, ('97', '18', '41', NULL , '2015-07-17', '2015-07-17')
, ('98', '18', '42', NULL , '2015-07-17', '2015-07-17')
, ('99', '18', '43', NULL , '2015-07-17', '2015-07-17')
, ('100', '18', '44', NULL , '2015-07-17', '2015-07-17')
, ('101', '18', '31', ''   , '2015-07-17', '2015-07-17')
, ('102', '18', '32', NULL , '2015-07-17', '2015-07-17')
, ('103', '18', '33', NULL , '2015-07-17', '2015-07-17')
, ('104', '18', '34', ''   , '2015-07-17', '2015-07-17')
, ('105', '18', '35', NULL , '2015-07-17', '2015-07-17')
, ('106', '18', '36', NULL , '2015-07-17', '2015-07-17')
, ('107', '18', '37', NULL , '2015-07-17', '2015-07-17')
, ('108', '18', '38', 'Yes', '2015-07-17', '2015-07-17')
;

CREATE TABLE public.questions (
  id bigserial PRIMARY KEY
, question text
, costperlead float8
);

INSERT INTO public.questions VALUES
  ('31', 'Content here..', '2.0')
, ('32', ' Content here.. ', '0')
, ('33', ' Content here.. ', '1.6')
, ('34', 'TEST_001', '5')
, ('35', 'TEST_001 Child 1', '1')
, ('36', 'TEST_001 Child 2', '2')
, ('37', 'TEST_001 Child 3', '3')
, ('38', 'Would you be interested in helping Diabetes UK improve diabetes care, treatment and prevention?', '1.5')
, ('39', 'Content here..', '2.4')
, ('40', ' Content here.. ', '3.2')
, ('41', ' Content here.. ', '1.3')
, ('42', ' Content here.. ', '3.1')
, ('43', ' Content here.. ', '1.2')
, ('44', ' Content here.. ', '4.3')
;
56 rows affected

14 rows affected

-- Postgres 9.4+
SELECT *, q.costperlead * (r.ct_yes + r.ct_maybe) AS revenue
FROM  (
   SELECT question_id
        , count(*) FILTER (WHERE response = 'Yes')      AS ct_yes
        , count(*) FILTER (WHERE response = 'No')       AS ct_no
        , count(*) FILTER (WHERE response = 'Possibly') AS ct_maybe
   FROM   responses
   WHERE  created_at = '2015-07-17'
   GROUP  BY 1
   ) r
JOIN   questions q ON q.id = r.question_id;


question_id
ct_yes
ct_no
ct_maybe
id
question
costperlead
revenue


31
2
0
0
31
Content here..
2
4

32
2
0
0
32
 Content here..
0
0

33
2
0
0
33
 Content here..
1.6
3.2

34
2
1
0
34
TEST_001
5
10

35
2
0
0
35
TEST_001 Child 1
1
2

36
2
0
0
36
TEST_001 Child 2
2
4

37
2
0
0
37
TEST_001 Child 3
3
6

38
3
1
0
38
Would you be interested in helping Diabetes UK improve diabetes care, treatment and prevention?
1.5
4.5

39
2
0
0
39
Content here..
2.4
4.8

40
2
0
0
40
 Content here..
3.2
6.4

41
2
0
0
41
 Content here..
1.3
2.6

42
2
0
0
42
 Content here..
3.1
6.2

43
2
0
0
43
 Content here..
1.2
2.4

44
2
0
0
44
 Content here..
4.3
8.6


-- Postgres 9.3-
SELECT *, q.costperlead * (r.ct_yes + r.ct_maybe) AS revenue
FROM  (
   SELECT question_id
        , count(response = 'Yes' OR NULL)      AS ct_yes
        , count(response = 'No' OR NULL)       AS ct_no
        , count(response = 'Possibly' OR NULL) AS ct_maybe
   FROM   responses
   WHERE  created_at = '2015-07-17'
   GROUP  BY 1
   ) r
JOIN   questions q ON q.id = r.question_id;


question_id
ct_yes
ct_no
ct_maybe
id
question
costperlead
revenue


31
2
0
0
31
Content here..
2
4

32
2
0
0
32
 Content here..
0
0

33
2
0
0
33
 Content here..
1.6
3.2

34
2
1
0
34
TEST_001
5
10

35
2
0
0
35
TEST_001 Child 1
1
2

36
2
0
0
36
TEST_001 Child 2
2
4

37
2
0
0
37
TEST_001 Child 3
3
6

38
3
1
0
38
Would you be interested in helping Diabetes UK improve diabetes care, treatment and prevention?
1.5
4.5

39
2
0
0
39
Content here..
2.4
4.8

40
2
0
0
40
 Content here..
3.2
6.4

41
2
0
0
41
 Content here..
1.3
2.6

42
2
0
0
42
 Content here..
3.1
6.2

43
2
0
0
43
 Content here..
1.2
2.4

44
2
0
0
44
 Content here..
4.3
8.6
question_id	ct_yes	ct_no	id	question	costperlead	revenue
31	2	0	31	Content here..	2	4
32	2	0	32	Content here..	0	0
33	2	0	33	Content here..	1.6	3.2
34	2	1	34	TEST_001	5	10
35	2	0	35	TEST_001 Child 1	1	2
36	2	0	36	TEST_001 Child 2	2	4
37	2	0	37	TEST_001 Child 3	3	6
38	3	1	38	Would you be interested in helping Diabetes UK improve diabetes care, treatment and prevention?	1.5	4.5
39	2	0	39	Content here..	2.4	4.8
40	2	0	40	Content here..	3.2	6.4
41	2	0	41	Content here..	1.3	2.6
42	2	0	42	Content here..	3.1	6.2
43	2	0	43	Content here..	1.2	2.4
44	2	0	44	Content here..	4.3	8.6