User Retention on OSGrid at Adam Frisby

Appendix – SQL Queries used in this post

SELECT CONCAT("Y",YEAR(signupdate),"W",WEEK(signupdate)), MAX(cnt1)/MAX(cnt4), MAX(cnt2)/MAX(cnt4), MAX(cnt3)/MAX(cnt4), MAX(cnt4)
FROM 
(
	SELECT * FROM 
	(
		SELECT (FROM_UNIXTIME(created)) as signupdate, COUNT(*) as cnt1, 0 as cnt2, 0 as cnt3, 0 as cnt4
		FROM users
		WHERE lastLogin != 0 AND created != 0 AND ((UNIX_TIMESTAMP() - lastLogin) / 86400) < 1.0
		GROUP BY YEARWEEK(FROM_UNIXTIME(created))
		ORDER BY created DESC
	) as DT1

UNION ALL

SELECT * FROM 
	(
		SELECT (FROM_UNIXTIME(created)) as signupdate, 0 as cnt1, COUNT(*) as cnt2, 0 as cnt3, 0 as cnt4
		FROM users
		WHERE lastLogin != 0 AND created != 0 AND ((UNIX_TIMESTAMP() - lastLogin) / 86400) < 7.0
		GROUP BY YEARWEEK(FROM_UNIXTIME(created))
		ORDER BY created DESC
	) as DT2

UNION ALL

SELECT * FROM 
	(
		SELECT (FROM_UNIXTIME(created)) as signupdate, 0 as cnt1, 0 as cnt2, COUNT(*) as cnt3, 0 as cnt4
		FROM users
		WHERE lastLogin != 0 AND created != 0 AND ((UNIX_TIMESTAMP() - lastLogin) / 86400) < 30.0
		GROUP BY YEARWEEK(FROM_UNIXTIME(created))
		ORDER BY created DESC
	) as DT3

UNION ALL

SELECT * FROM 
	(
		SELECT (FROM_UNIXTIME(created)) as signupdate, 0 as cnt1, 0 as cnt2, 0 as cnt3, COUNT(*) as cnt4
		FROM users
		WHERE lastLogin != 0 AND created != 0
		GROUP BY YEARWEEK(FROM_UNIXTIME(created))
		ORDER BY created DESC
	) as DT4

) as MT
GROUP BY YEARWEEK(signupdate)

SELECT CONCAT("Y",YEAR(signupdate),"W",WEEK(signupdate)), MAX(cnt1), MAX(cnt2)
FROM 
(
	SELECT * FROM 
	(
		SELECT (FROM_UNIXTIME(created)) as signupdate, COUNT(*) as cnt1, 0 as cnt2, 0 as cnt3, 0 as cnt4
		FROM users
		WHERE lastLogin != 0 AND created != 0 AND ((UNIX_TIMESTAMP() - lastLogin) / 86400) < 30.0
		AND UUID IN (SELECT owner_uuid FROM regions)
		GROUP BY YEARWEEK(FROM_UNIXTIME(created))
		ORDER BY created DESC
	) as DT1

UNION ALL

SELECT * FROM 
	(
		SELECT (FROM_UNIXTIME(created)) as signupdate, 0 as cnt1, COUNT(*) as cnt2, 0 as cnt3, 0 as cnt4
		FROM users
		WHERE lastLogin != 0 AND created != 0 AND ((UNIX_TIMESTAMP() - lastLogin) / 86400) < 30.0
		AND UUID NOT IN (SELECT owner_uuid FROM regions)
		GROUP BY YEARWEEK(FROM_UNIXTIME(created))
		ORDER BY created DESC
	) as DT2
) as MT
GROUP BY YEARWEEK(signupdate)

SELECT CONCAT("Y",YEAR(signupdate),"W",WEEK(signupdate)), MAX(cnt1), MAX(cnt2)
FROM 
(
	SELECT * FROM 
	(
		SELECT (FROM_UNIXTIME(created)) as signupdate, COUNT(*) as cnt1, 0 as cnt2, 0 as cnt3, 0 as cnt4
		FROM users
		WHERE UUID IN (SELECT owner_uuid FROM regions)
		GROUP BY YEARWEEK(FROM_UNIXTIME(created))
		ORDER BY created DESC
	) as DT1

UNION ALL

SELECT * FROM 
	(
		SELECT (FROM_UNIXTIME(created)) as signupdate, 0 as cnt1, COUNT(*) as cnt2, 0 as cnt3, 0 as cnt4
		FROM users
		WHERE UUID NOT IN (SELECT owner_uuid FROM regions)
		GROUP BY YEARWEEK(FROM_UNIXTIME(created))
		ORDER BY created DESC
	) as DT2
) as MT
GROUP BY YEARWEEK(signupdate)

1 Vote

Feedback

If you found this post useful and want me to write more on this topic, please use the vote button to the left or leave me a comment below.

Written by Adam Frisby

May 9th, 2009 at 9:41 am

Posted in Uncategorized

5 Responses to 'User Retention on OSGrid'

Subscribe to comments with RSS or TrackBack to 'User Retention on OSGrid'.

Nice work as usual Adam

Cheers
James

daTwitch

9 May 09 at 11:19 am
Well done mate keep up the good work

SP

Simon Probert

11 May 09 at 2:14 pm
BTW I got lost soon after the “SELECT” in your queries page… in fact it was immediately after the SELECT

*grins*

Simon Probert

11 May 09 at 2:19 pm
That’s a big analysis on a small dataset. I wonder when LL will start publishing land sales data? Or is that also a small dataset since the openspace scam?

rightasrain

11 May 09 at 5:34 pm
Lol rightastrain, opensace scam is right. I thought they will ever publish the land sales data.

ottalese

26 May 09 at 3:36 pm

Appendix – SQL Queries used in this post

Feedback

5 Responses to 'User Retention on OSGrid'

Leave a Reply

Pages

Shameless Plug

Recent Posts

Categories

Cumulus

Tags

Meta

Most Voted Posts

You need to log in to vote