nps_queries.sql

/*NPS ROLLING 30-DAYS AVG AND DAILY SCORE CHART*/
SELECT a.date,
	a.net_promoters / a.responses * 100 AS score,
	(SUM(b.net_promoters)
		 /
	SUM(b.responses)) * 100 AS score_30d
FROM (
SELECT date(s.created_at) AS date,
	SUM(CASE WHEN s.score >= 9 THEN 1.0
			WHEN s.score <= 6 THEN -1.0
			ELSE 0 END) net_promoters,
  	SUM(CASE WHEN s.score >= 0 THEN 1.0 ELSE 0 END) AS responses
	FROM surveys_responses s
	GROUP BY date
ORDER BY date ) a
JOIN (SELECT date(s.created_at) as date,
	SUM(CASE WHEN s.score >= 9 THEN 1.0
			WHEN s.score <= 6 THEN -1.0
			ELSE 0 END) net_promoters,
  	SUM(CASE WHEN s.score >= 0 THEN 1.0 ELSE 0 END) AS responses
	FROM surveys_responses s
	GROUP BY date
ORDER BY date ) b ON b.date BETWEEN a.date -29 AND a.date
GROUP BY a.date, score

/*NPS ROLLING 30-DAYS AVG PERCENT OF RESPONSES BY TYPE CHART*/
SELECT a.date,
	(SUM(b.promoters) /
	SUM(b.responses)) * 100 AS promoters_30d,
	(SUM(b.passives) /
	SUM(b.responses)) * 100 AS passives_30d,
	(SUM(b.detractors) /
	SUM(b.responses)) * 100 AS detractors_30d
FROM (
SELECT date(s.created_at) as date,
	SUM(CASE WHEN s.score >= 9 THEN 1.0 ELSE 0 END) as promoters,
	SUM(CASE WHEN s.score in (7,8) THEN 1.0 ELSE 0 END) as passives,
	SUM(CASE WHEN s.score <= 6 THEN 1.0 ELSE 0 END) as detractors,
  	SUM(CASE WHEN s.score >= 0 THEN 1.0 ELSE 0 END) as responses
	FROM surveys_responses s
	GROUP BY date
ORDER BY date ) a
JOIN (
SELECT date(s.created_at) as date,
	SUM(CASE WHEN s.score >= 9 THEN 1.0 ELSE 0 END) as promoters,
	SUM(CASE WHEN s.score in (7,8) THEN 1.0 ELSE 0 END) as passives,
	SUM(CASE WHEN s.score <= 6 THEN 1.0 ELSE 0 END) as detractors,
  	SUM(CASE WHEN s.score >= 0 THEN 1.0 ELSE 0 END) as responses
	FROM surveys_responses s
	GROUP BY date
ORDER BY date ) b ON b.date BETWEEN a.date -29 AND a.date
GROUP BY a.date

/*NPS ROLLING 30-DAYS TOTAL AND DAILY NUMBER OF RESPONSES CHART*/
SELECT a.date,
	a.responses as responses,
	SUM(b.responses) AS responses_30d
FROM (
SELECT date(s.created_at) as date,
  	SUM(CASE WHEN s.score >= 0 THEN 1.0 ELSE 0 END) as responses
	FROM surveys_responses s
	GROUP BY date
ORDER BY date ) a
JOIN (
SELECT date(s.created_at) as date,
  	SUM(CASE WHEN s.score >= 0 THEN 1.0 ELSE 0 END) as responses
	FROM surveys_responses s
	GROUP BY date
ORDER BY date ) b ON b.date BETWEEN a.date -29 AND a.date
GROUP BY a.date, a.responses

/*NPS ROLLING 30-DAYS AVG AND DAILY RESPONSE RATE CHART*/
SELECT a.date,
	a.responses / a.surveys * 100 as response_rate,
	(SUM(b.responses) /
	 SUM(b.surveys)) * 100 AS response_rate_30d
FROM (
SELECT date(s.created_at) as date,
  	SUM(CASE WHEN s.score >= 0 THEN 1.0 ELSE 0 END) as responses,
  	COUNT(*) as surveys
	FROM surveys_responses s
	GROUP BY date
ORDER BY date ) a
JOIN (
SELECT date(s.created_at) as date,
  	SUM(CASE WHEN s.score >= 0 THEN 1.0 ELSE 0 END) as responses,
  	COUNT(*) as surveys
	FROM surveys_responses s
	GROUP BY date
ORDER BY date ) b ON b.date BETWEEN a.date -29 AND a.date
GROUP BY a.date, response_rate

/*NPS COMMENTS IN THE PAST 14 DAYS FOR YOUR SPREADSHEET*/
SELECT date_format(s.created_at, '%x-%v') as week,
	s.feedback,
	s.score
FROM surveys_responses s
WHERE s.feedback IS NOT NULL
AND created_at >= date_sub(now(), INTERVAL 14 DAY)
ORDER BY s.created_at

I was using this queries and found that the BETWEEN clause works but is incorrect. Should be used as this:

BETWEEN a.date - INTERVAL 29 DAY AND a.date

this way it works intra-months, otherwise it did a strange reset every start of the month.