CODEWITHSUNDEEP
sql
Alex Borsody
Alex Borsody

Reputation: 2040

select repeat visitors given two time periods

I would like to find the percent of repeat visitors to my site. Currently I am selecting users for one month and dividing that month by the following month. to calculate those that have returned. Is this the best way to write that query?

it is yielding results that seem to be accurate, but wondering if there is a more elegant solution.

SELECT (
SELECT COUNT(table.user) as Total
FROM table
WHERE table.event IN ('event1','event2','event3')
AND table.month IN ('october')
) /
(
SELECT COUNT(table.user) as newTotal
FROM table
WHERE table.event IN ('event1','event2','event3')
AND (table.month IN ('october') OR table.month IN ('november'))
) AS percent_return

table structure looks like this, you have the same user purchasing multiple events for the same month or the same time period. 

 order_number user month event gross

   1 jack october event2 30
   2 jack november event3 20
   3 jack november event3 20
   4 jack november event2 30
   5 sam november event2 30
   6 john october event3 20
   7 john october non_event 20

Upvotes: 1

Views: 240

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You don't need two subqueries to do what you want. Just use conditional aggregation:

SELECT (SUM(s.month IN (october)) / SUM(s.month IN (october, november))
       ) as percent_return
FROM sales s
WHERE s.event IN ('event1', 'event2', 'event3') AND
      s.gross > 0;

Also, there is no need for single quotes around numeric constants.

Upvotes: 1

Related Questions