This is a nice little puzzle that I got some time ago from a friend and colleague, Eladio Rincón. It’s not a very difficult challenge, but I find it to be a fun one to work on, so I hope you will enjoy it too.
Suppose that you record data about people’s daily visits to a website in a table called DailyVisits. Here’s code to create the DailyVisits table and populate it with some sample data:
-- DDL and sample data for DailyVisits table
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.DailyVisits', 'U') IS NOT NULL
DROP TABLE dbo.DailyVisits;
GO
CREATE TABLE dbo.DailyVisits
(
dt DATE NOT NULL,
visitor VARCHAR(10) NOT NULL,
CONSTRAINT PK_DailyVisits PRIMARY KEY(dt, visitor)
);
INSERT INTO dbo.DailyVisits(dt, visitor) VALUES
('20110601', 'A'),
('20110601', 'B'),
('20110601', 'C'),
--
('20110602', 'A'),
('20110602', 'C'),
--
('20110603', 'A'),
('20110603', 'D'),
--
--
('20110607', 'A'),
('20110607', 'D'),
--
('20110608', 'D'),
('20110608', 'E'),
('20110608', 'F');
SELECT *
FROM dbo.DailyVisits;
dt visitor
---------- ----------
2011-06-01 A
2011-06-01 B
2011-06-01 C
2011-06-02 A
2011-06-02 C
2011-06-03 A
2011-06-03 D
2011-06-07 A
2011-06-07 D
2011-06-08 D
2011-06-08 E
2011-06-08 F
Observe that there’s a key defined on dt and visitor, meaning that you store only one occurrence at most per visitor and day. Also note that there is a possibility that during some days there would be no visitors.
The challenge is to write a query that works with two input arguments @from and @to holding the start and end dates of a date range, and calculates, for each day, various statistics in respect to the previous day. Specifically, how many visitors visited the site that day, how many new visitors were added compared to the previous day, how many visitors were removed compared to the previous day, and how many remained. For the given sample data, the desired result should look like this:
dt numvisits added removed remained
---------- ----------- ----------- ----------- -----------
2011-06-01 3 3 0 0
2011-06-02 2 0 1 2
2011-06-03 2 1 1 1
2011-06-04 0 0 2 0
2011-06-05 0 0 0 0
2011-06-06 0 0 0 0
2011-06-07 2 2 0 0
2011-06-08 3 2 1 1
Feel free to post your solution as a comment here. I’ll post an entry next week with my solution.
Good luck!
BG
