First time contributor return ratio

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

DECLARE @FirstContributeStart varchar(10);
DECLARE @FirstContributeEnd varchar(10);
DECLARE @RevisitRangeStart varchar(10);
DECLARE @RevisitRangeEnd varchar(10);

DECLARE @N1 int;
DECLARE @N2 int;
DECLARE @PostType int = 2; /* Answers */
CREATE TABLE #TT ( uid int );

SET @FirstContributeStart = '2019-04-01';
SET @FirstContributeEnd = '2019-04-30';
SET @RevisitRangeStart = '2019-07-01';
SET @RevisitRangeEnd = '2019-07-31';

INSERT INTO #TT (uid)
  SELECT 
    p.OwnerUserId
  FROM Posts AS p
  WHERE 
    p.PostTypeId = @PostType AND
    CAST(p.CreationDate AS Date) >= CAST (@FirstContributeStart as DATE) AND 
    CAST(p.CreationDate AS Date) <= CAST (@FirstContributeEnd as DATE) AND 
    NOT EXISTS (
      SELECT 
        p2.OwnerUserId AS [uid]
      FROM Posts AS p2
      WHERE 
        p.OwnerUserId=p2.OwnerUserId AND
        p2.PostTypeId = @PostType AND
        CAST(p2.CreationDate AS Date) < CAST (@FirstContributeStart as DATE)
    )
  GROUP BY p.OwnerUserId


SET @N1 = (SELECT COUNT(t1.uid) FROM #TT AS [t1]);
SET @N2 = (
  SELECT 
    COUNT(t2.uid)
  FROM (
    SELECT 
      p.OwnerUserId AS [uid]
    FROM Posts  AS p
    WHERE 
      p.PostTypeId = 1 AND
      CAST(p.CreationDate AS Date) >= CAST (@RevisitRangeStart as DATE) AND
      CAST(p.CreationDate AS Date) <= CAST (@RevisitRangeEnd as DATE) AND
      p.OwnerUserId IN (SELECT uid FROM #TT)
    GROUP BY p.OwnerUserId
  ) AS [t2]
);


PRINT '---------';
PRINT 'Total # of user contributed first time in between ';
PRINT @FirstContributeStart;
PRINT 'to';
PRINT @FirstContributeEnd;
PRINT 'is :';
PRINT @N1;

PRINT '---------';
PRINT @N2;
PRINT 'of these users also contributed in between';
PRINT @RevisitRangeStart;
PRINT 'to';
PRINT @RevisitRangeEnd;
PRINT '---------';

PRINT 'Return Rate is :';
PRINT (@N2 * 100 ) / @N1 ;
PRINT 'percent';
PRINT '---------';

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results