question

Matt Whitfield avatar image
Matt Whitfield asked

Phil Factor SQL Speed Phreak Challenge #4 - The Log Parsing Problem

It must be that time again!

Phil Factor SQL Speed Phreak Challenge #4 is here, and our friends at Red Gate have offered some truly excellent prizes this time:

First Prize: $100 and a license for SQL Data Generator

There will also be three categorised runner-up prizes:

  • Best Cursor-based solution
  • Best CLR solution
  • Best Unusual/experimental attempt

The winners of these prizes will get a licensed copy of either SQL Prompt or SQL Refactor, whichever they prefer.

Of course, the first place entry will also have privilege and honour of displaying this rather nice trophy jpg on your blog / website / bedroom wall in perpetuity.

Speed Phreak Trophy

So, the task...

It turns out that Robert Bar has a younger brother. Reggie Bar was asked by his company to so some analytics work on their web logs. Reggie started by using the information in this article to use LogParser to import the data from his IIS logs into the database. So far, so good.

Reggie then got hold of a geolocation IP database from here. Again, so far, so good.

That’s where the good times ended, however. Reggie decided that the best way to approach this task was to use cursors, and even on a small amount of data the solution is taking longer and longer to execute. Management aren’t happy, they need a solution that is fast and won’t get hugely slower over time.

The data

The main data sources are two standard sources of information – the output of LogParser (in the table [dbo].[IISAudit]) and the geolocation IP address (in the table [dbo].[ip_group_country]). The data for both of these tables is kindly being hosted by Peso here. Also included in the data file are the DDL for the tables, the BCP commands to import the data, and Reggie’s solution to the problem.

The data (update)

There is now available another table IISAudit2, which is exactly the same as the old table, except for the fact that it adds a sequence number to the data, to aid sorting the rows in a consistent and repeatable manner (something you would probably do if loading this data in from your own IIS server anyway). Feel free to use this in your solutions if it makes it easier for you - the file is available (again kindly hosted by Peso) here.

The data (update 2)

Peso found a bug in Reggie's code, where the lookup on the ip_group_country got it wrong for one value because it used < rather than <=. New code, both data files, DDL & bcp commands, again kindly hosted by Peso, are here.

The output

The output required is three result sets:

  • Visitor summary by country by day
  • Visitor summary by country
  • Page summary by day

Note that in this challenge, the order of the data in the result sets does matter.

The visitor summaries include the following calculations:

  • VisitCount – this is the number of unique visits. A visit is one or more page requests from the same IP address within a set time limit. The time limit is configurable through the @SessionTimeout parameter in Reggie’s solution. Note that only pages with the extensions .asp, .aspx, .htm, .html and .php are considered as page views.
  • PageCount – this is the total number of pages viewed. Again note that only pages with the extensions .asp, .aspx, .htm, .html and .php are considered as page views.
  • MaxPagesPerVisit – this is the highest number of pages per visit.
  • AvgPagesPerVisit – this is the average number of pages per visit, to 2 decimal places.
  • MaxTimeOnSite – this is the maximum time that any particular visitor spent on the site. The time on site is defined as the difference between the first and last page load for a particular visit.
  • AvgTimeOnSite - this is the average time that any particular visitor spent on the site.
  • GoalCount – this is the number of times that any of a particular set of pages were loaded. A single visit may build up multiple goals, and viewing the same goal page multiple times counts as multiple goals.
  • BounceRate – this is the number of visits where only a single page was loaded, divided by the total number of visits, and expressed as a percentage to two decimal places.

The page summary includes the following calculations:

  • PageCount – this is the number of times that the page was loaded that day.
  • EntryRate – this is the number of times that the page was the first page of the visit, divided by PageCount, and expressed as a percentage to two decimal places.
  • ExitRate – this is the number of times that the page was the last page of the visit, divided by PageCount, and expressed as a percentage to two decimal places.
  • BounceRate – this is the number of times that the page was both the first and the last page of the visit, divided by PageCount, and expressed as a percentage to two decimal places.

Note that for the country summary by day report, the day to which a visit is added is the day on which the visit started.

To lookup the geolocation data for an IP A.B.C.D, you need to find the row in [dbo].[ip_group_country] that has the highest [ip_start] value that is less than or equal to:

(A * 16777216) + (B * 65536) + (C * 256) + D

The rules

The rules for the challenge are here.

The finishing date: Midnight GMT 28th February 2010

Submissions must be in by 28th Feb 2010

Bootnote

Reggie Bar's solution will only return the correct result from a table loaded directly from the source data. He really should have put an order by on the cursor select, shouldn't he? *cough*

Leaderboard

+---------------------------------+----------+----------+----------+----------+----------+----------+
| Name                            | Min      | Avg      | Med      | Max      | Total    | Runs     |
+---------------------------------+----------+----------+----------+----------+----------+----------+
| Matts CLR Not An Entry          |      292 |      376 |      301 |    1,807 |    7,522 |       20 |
| Peso 2c                         |    1,718 |    1,863 |    1,779 |    3,572 |   37,263 |       20 |
| Peso 2a (Invalid)               |    1,681 |    2,385 |    1,925 |    4,745 |   47,706 |       20 |
| Dave1b (Almost Valid!!)         |    2,259 |    2,466 |    2,269 |    5,967 |   49,334 |       20 |
| Dave1a (Invalid)                |    2,469 |    2,533 |    2,483 |    3,287 |   50,660 |       20 |
| Peso 3a (Invalid)               |    2,697 |    2,863 |    2,723 |    4,927 |   57,279 |       20 |
| Peso 1a 20100216 (Invalid)      |    2,855 |    3,020 |    2,866 |    4,037 |   60,417 |       20 |
| Phil 1b (Invalid)               |    3,743 |    4,013 |    3,865 |    6,788 |   80,275 |       20 |
| Phil 1a (Invalid)               |    5,040 |    5,422 |    5,172 |   10,274 |  108,451 |       20 |
+---------------------------------+----------+----------+----------+----------+----------+----------+

Result

Congratulations to Peso - his 2c entry is the winner!

t-sqlsql-serverchallengepuzzlespeed-phreak
12 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

A visit is one or more page requests from the same IP address within a set time limit...We should include POSTs as a page request, correct?
0 Likes 0 ·
Scot - correct - any sort of activity from that same IP keeps the session going...
0 Likes 0 ·
I wouldn't do this in reality, because an entire enterprise or ISP can share an IP address. You actually have to look closely at the UserAgent to work out individual visitors as well as the IP address, but I suspect that Reggie didn't know about that.
0 Likes 0 ·
Agreed - Ideally you'd get the session ID and use that...
0 Likes 0 ·
Even looking at UserAgent isn't the ideal way Phil because in an enterprise they all tend to be on the same one. Session ID is a good alternative however if I tend to have a requirement for user tracking through a system I have some code that saves user details on a page load ( ASP.NET/C#) that stores details of the user logged in or otherwise which I can then correlate to the stats.
0 Likes 0 ·
Show more comments
Peso avatar image
Peso answered

Peso 2c 2010-02-23

Same setup and teardown as Peso 2a (single index).

The main code

SET NOCOUNT ON

DECLARE @SessionTimeout INT = 900

CREATE TABLE    #Goal
        (
            theURL VARCHAR(255),
            CONSTRAINT PK_Goal PRIMARY KEY CLUSTERED
            (
                theURL
            )
        )

INSERT  #Goal
    (
        theURL
    )
VALUES  ('ASPNETPage29.aspx'),
    ('ASPNETPage9.aspx')

CREATE TABLE    #Stats
        (
            RowID INT IDENTITY(1, 1),
            CONSTRAINT PK_Stats PRIMARY KEY CLUSTERED
            (
                RowID
            ), 
            cIP VARCHAR(15) NOT NULL,
            DateStr CHAR(10) NOT NULL,
            PageTime DATETIME NOT NULL,
            isGoal TINYINT NOT NULL,
            theURL VARCHAR(255) NOT NULL,
            CountryCode CHAR(2) NOT NULL,
            theVisit INT,
            isBounce TINYINT,
            isEntry TINYINT,
            isExit TINYINT,
            [PageCount] INT,
            theTime INT
        )

INSERT      #Stats
        (
            cIP,
            DateStr,
            PageTime,
            isGoal,
            theURL,
            CountryCode
        )
SELECT      i.cIP,
        i.DateStr,
        CAST(i.DateStr + ' ' + i.TimeStr AS DATETIME) AS PageTime,
        CASE
            WHEN g.theURL IS NULL THEN 0
            ELSE 1
        END AS isGoal,
        i.csUriStem AS theURL,
        igc.CountryCode
FROM        dbo.IISAudit2 AS i
LEFT JOIN   #Goal AS g ON g.theURL = i.csUriStem
CROSS APPLY (
            SELECT TOP(1)   w.Country_Code
            FROM        dbo.Ip_Group_Country AS w
            WHERE       w.ip_start <= CAST(16777216 AS BIGINT) * PARSENAME(i.cIP, 4) + 65536 * PARSENAME(i.cIP, 3) + 256 * PARSENAME(i.cIP, 2) + PARSENAME(i.cIP, 1)
            ORDER BY    w.ip_start DESC
        ) AS igc(CountryCode)
WHERE       PARSENAME(i.csUriStem, 1) IN ('asp', 'aspx', 'htm', 'html', 'php')
ORDER BY    i.cIP,
        i.Seq_No

DROP TABLE #Goal

DECLARE @IP VARCHAR(15),
    @PageTime DATETIME,
    @Visit INT = 0

UPDATE  #Stats
SET @Visit = theVisit = CASE
                    WHEN cIP <> @IP THEN @Visit + 1
                    WHEN DATEDIFF(SECOND, @PageTime, PageTime) < @SessionTimeout THEN @Visit
                    ELSE @Visit + 1
                END,
    @IP = cIP,
    @PageTime = PageTime

UPDATE      s
SET     s.isBounce =    CASE 
                    WHEN q.minID = s.RowID THEN q.isBounce
                    END,
        s.isEntry = CASE
                    WHEN q.minID = s.RowID THEN 1
                END,
        s.isExit =  CASE
                    WHEN q.maxID = s.RowID THEN 1
                END,
        s.[PageCount] = CASE
                    WHEN q.minID = s.RowID THEN q.[PageCount]
                END,
        s.theTime = CASE
                    WHEN q.minID = s.RowID THEN q.theTime
                END,
        s.PageTime = q.theDate
FROM        #Stats AS s
INNER JOIN  (
            SELECT      w.theVisit,
                    MIN(w.minID) AS minID,
                    MAX(w.maxID) AS maxID,
                    CASE MAX(CASE WHEN s.RowID = w.minID THEN s.theURL ELSE NULL END)
                        WHEN MAX(CASE WHEN s.RowID = w.maxID THEN s.theURL ELSE NULL END) THEN 1
                        ELSE 0
                    END AS isBounce,
                    COUNT(*) AS [PageCount],
                    MAX(w.theTime) AS theTime,
                    DATEDIFF(DAY, 0, MIN(theDate)) AS theDate
            FROM        (
                        SELECT      theVisit,
                                MIN(RowID) AS minID,
                                MAX(RowID) AS maxID,
                                DATEDIFF(SECOND, MIN(PageTime), MAX(PageTime)) AS theTime,
                                MIN(PageTime) AS theDate
                        FROM        #Stats
                        GROUP BY    theVisit
                    ) AS w
            INNER JOIN  #Stats AS s ON s.theVisit = w.theVisit
            GROUP BY    w.theVisit
        ) AS q ON q.theVisit = s.theVisit

-- 1
SELECT      CountryCode,
        PageTime AS DayOnly,
        COUNT(DISTINCT theVisit) AS VisitCount,
        SUM([PageCount]) AS [PageCount],
        MAX([PageCount]) AS MaxPagesPerVisit,
        CAST(1.0E * SUM([PageCount]) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
        MAX(theTime) AS MaxTimeOnSite,
        SUM(theTime) / COUNT(DISTINCT theVisit) AS AvgTimeOnSite,
        SUM(isGoal) AS GoalCount,
        CAST(100.0E * SUM(CASE WHEN [PageCount] = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Stats
GROUP BY    CountryCode,
        PageTime
ORDER BY    PageTime,
        CountryCode

-- 2
SELECT      CountryCode,
        COUNT(DISTINCT theVisit) AS VisitCount,
        SUM([PageCount]) AS [PageCount],
        MAX([PageCount]) AS MaxPagesPerVisit,
        CAST(1.0E * SUM([PageCount]) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
        MAX(theTime) AS MaxTimeOnSite,
        SUM(theTime) / COUNT(DISTINCT theVisit) AS AvgTimeOnSite,
        SUM(isGoal) AS GoalCount,
        CAST(100.0E * SUM(CASE WHEN [PageCount] = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Stats
GROUP BY    CountryCode
ORDER BY    CountryCode

-- 3
SELECT      uriStem,
        PageDay,
        SUM([PageCount]) AS [PageCount],
        CAST(100.0E * SUM(isEntry) / SUM([PageCount]) AS DECIMAL(12, 2)) AS EntryRate,
        CAST(100.0E * SUM(isExit) / SUM([PageCount]) AS DECIMAL(12, 2)) AS ExitRate,
        CAST(100.0E * SUM(isBounce) / SUM([PageCount]) AS DECIMAL(12, 2)) AS BounceRate
FROM        (
            SELECT      theURL AS uriStem,
                    PageTime AS PageDay,
                    0 AS [PageCount],
                    SUM(isEntry) AS isEntry,
                    SUM(isExit) AS isExit,
                    SUM(isBounce) AS isBounce
            FROM        #Stats
            GROUP BY    PageTime,
                    theUrl

            UNION ALL

            SELECT      theURL AS uriStem,
                    DateStr AS PageDay,
                    COUNT(*) AS [PageCount],
                    0 AS isEntry,
                    0 AS isExit,
                    0 AS isBounce
            FROM        #Stats
            GROUP BY    DateStr,
                    theUrl
        ) AS d
GROUP BY    PageDay,
        uriStem
ORDER BY    PageDay,
        uriStem

DROP TABLE  #Stats
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke avatar image
Ian Roke answered

If I make use of the new SQL Server 2008 Date and Time data types would that be wrong because it doesn't downgrade gracefully to SQL Server 2005 and backwards?

28 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

No, you wouldn't be penalized for using them, but I doubt whether it would give an enormous performance boost.
0 Likes 0 ·
Well, my current try executes in 4 seconds, but it's hard to compare since I get different result from the code by Reggie Bar. I see two things missing in Mr Bar's code: 1) Missing "ORDER BY cIP, DateStr, TimeStr" in the CURSOR declaration 2) Missing "AND LastPageTime < @thisPageLoadTime" in the SELECT TOP(1) part.
0 Likes 0 ·
Well played Peso I have got three seconds execution time so far but I have only done so much of it. Four seconds is impressive at this stage from over three minutes for Reggie!
0 Likes 0 ·
@Peso - as I put in the bootnote - the query will only work from reggie when the rows are heaped/clustered in their natual order. The clause you want for the cursor is ORDER BY convert(datetime, datestr), timestr. The second bit is not needed given the first bit :)
0 Likes 0 ·
@Peso - p.s. how did I know you'd be coming in with a ridiculously quick solution?? :)
0 Likes 0 ·
Show more comments
Peso avatar image
Peso answered

Ok, I'll go first then. I am sure this will give some ideas to other how to solve different tasks. This is "Peso 1a 20100216". Matt, start to time this to give some sort of baseline.

The setup is extensive

ALTER TABLE dbo.IISAudit
ALTER COLUMN    DateStr DATETIME

ALTER TABLE dbo.IISAudit
ALTER COLUMN    TimeStr DATETIME

ALTER TABLE dbo.IISAudit
ADD 	TimeSpan AS CAST(86400 AS BIGINT) * DATEDIFF(DAY, 0, DateStr) + DATEDIFF(SECOND, 0, TimeStr) PERSISTED

ALTER TABLE dbo.IISAudit
ADD 	pageTime AS DateStr + TimeStr PERSISTED

ALTER TABLE dbo.IISAudit
ADD 	ValidExt AS	CASE SUBSTRING(csUriStem, CHARINDEX('.', csUriStem), 5)
    				WHEN '.asp' THEN 1
    				WHEN '.aspx' THEN 1
    				WHEN '.htm' THEN 1
    				WHEN '.html' THEN 1
    				WHEN '.php' THEN 1
    				ELSE 0
    			END PERSISTED

CREATE INDEX    IX_ValidExt
ON  	dbo.IISAudit
    	(
    		ValidExt
    	)
INCLUDE 	(
    		cIP,
    		TimeSpan,
    		DateStr,
    		pageTime,
    		csUriStem
    	)

CREATE UNIQUE
INDEX   	UCX_IP
ON  	dbo.ip_group_country
    	(
    		ip_start
    	)
INCLUDE 	(
    		country_code
    	)

The teardown is just ridicolous

DROP INDEX  IX_ValidExt
ON  	dbo.IISAudit

ALTER TABLE dbo.IISAudit
DROP COLUMN ValidExt

ALTER TABLE dbo.IISAudit
DROP COLUMN pageTime

ALTER TABLE dbo.IISAudit
DROP COLUMN TimeSpan

DROP INDEX  UCX_IP
ON  	dbo.ip_group_country

ALTER TABLE dbo.IISAudit
ADD 	wTemp CHAR(10)

GO

UPDATE  dbo.IISAudit
SET wTemp = CONVERT(CHAR(10), DateStr, 120)

ALTER TABLE dbo.IISAudit
ALTER COLUMN    DateStr VARCHAR(255)

UPDATE  dbo.IISAudit
SET DateStr = wTemp

UPDATE  dbo.IISAudit
SET wTemp = CONVERT(CHAR(8), TimeStr, 8)

ALTER TABLE dbo.IISAudit
ALTER COLUMN    TimeStr VARCHAR(255)

UPDATE  dbo.IISAudit
SET TimeStr = wTemp

ALTER TABLE dbo.IISAudit
DROP COLUMN wTemp

And here is the main part

SET NOCOUNT ON

DECLARE @SessionTimeout INT = 900

CREATE TABLE    #Goal
    	(
    		theURL VARCHAR(255),
    		CONSTRAINT PK_Goal PRIMARY KEY CLUSTERED
    		(
    			theURL
    		)
    	)

INSERT  #Goal
    (
    	theURL
    )
VALUES  ('ASPNETPage29.aspx'),
    ('ASPNETPage9.aspx')

CREATE TABLE    #Stats
    	(
    		theIP VARCHAR(15),
    		visitID INT,
    		pageDate DATETIME,
    		pageTime DATETIME,
    		theURL VARCHAR(255),
    		isGoal TINYINT,
    		Country_Code VARCHAR(2),
    		theFirst INT,
    		Bounce INT,
    		MaxPages INT,
    		theTime INT
    	)

CREATE INDEX IX_Stats ON #Stats (theIP, visitID, pageTime)

INSERT  	#Stats
    	(
    		theIP,
    		visitID,
    		pageDate,
    		pageTime,
    		theURL,
    		isGoal,
    		Bounce
    	)
SELECT  	i.cIP AS theIP,
    	i.TimeSpan / @SessionTimeout AS VisitID,
    	i.DateStr AS pageDate,
    	i.pageTime,
    	i.csUriStem AS theURL,
    	CASE
    		WHEN g.theURL IS NULL THEN 0
    		ELSE 1
    	END AS isGoal,
    	0 AS Bounce
FROM    	dbo.IISAudit AS i
LEFT JOIN   #Goal AS g ON g.theURL = i.csUriStem
WHERE   	i.ValidExt = 1

DROP TABLE  #Goal

DECLARE curYak CURSOR FOR
    	SELECT		l.theIP,
    			l.visitID AS fromID,
    			r.visitID AS toID
    	FROM		(
    				SELECT		theIP,
    						visitID,
    						MAX(pageTime) AS pageTime
    				FROM		#Stats
    				GROUP BY	theIP,
    						visitID
    			) AS l
    	INNER JOIN	(
    				SELECT		theIP,
    						visitID,
    						MIN(pageTime) AS pageTime
    				FROM		#Stats
    				GROUP BY	theIP,
    						visitID
    			) AS r ON r.theIP = l.theIP
    				AND r.visitID = l.visitID + 1
    	WHERE		DATEDIFF(SECOND, l.pageTime, r.pageTime) <= @SessionTimeout
    	ORDER BY	l.visitID

DECLARE @IP VARCHAR(15),
    @fromID INT,
    @toID INT

OPEN    curYak

FETCH   NEXT
FROM    curYak
INTO    @IP,
    @fromID,
    @toID

WHILE @@FETCH_STATUS = 0
    BEGIN
    	UPDATE	#Stats
    	SET	visitID = @toID
    	WHERE	theIP = @IP
    		AND visitID = @fromID

    	FETCH	NEXT
    	FROM	curYak
    	INTO	@IP,
    		@fromID,
    		@toID
    END

CLOSE   	curYak
DEALLOCATE  curYak

CREATE TABLE    #Lookup
    	(
    		theIP VARCHAR(15),
    		Country_Code VARCHAR(2)
    	)

INSERT  	#Lookup
    	(
    		theIP
    	)
SELECT  	theIP
FROM    	#Stats
GROUP BY    theIP

UPDATE  	l
SET 	l.Country_Code = igc.Country_Code
FROM    	#Lookup AS l
CROSS APPLY (
    		SELECT TOP(1)	w.Country_Code
    		FROM		dbo.Ip_Group_Country AS w
    		WHERE		w.ip_start <= CAST(16777216 AS BIGINT) * PARSENAME(l.theIP, 4) + 65536 * PARSENAME(l.theIP, 3) + 256 * PARSENAME(l.theIP, 2) + PARSENAME(l.theIP, 1)
    		ORDER BY	w.ip_start DESC
    	) AS igc

UPDATE  	s
SET 	s.Country_Code = l.Country_Code
FROM    	#Stats AS s
INNER JOIN  #Lookup AS l ON l.theIP = s.theIP	

DROP TABLE  #Lookup

UPDATE  f
SET theFirst = a,
    MaxPages = t,
    theTime = DATEDIFF(SECOND, theMin, theMax)
FROM    (
    	SELECT	theFirst,
    		ROW_NUMBER() OVER (PARTITION BY theIP, VisitID ORDER BY PageTime) AS a,
    		MaxPages,
    		COUNT(theURL) OVER (PARTITION BY theIP, visitID) AS t,
    		theTime,
    		MIN(pageTime) OVER (PARTITION BY theIP, visitID) AS theMin,
    		MAX(pageTime) OVER (PARTITION BY theIP, visitID) AS theMax
    	FROM	#Stats
    ) AS f

UPDATE  	s
SET 	s.Bounce = 1
FROM    	#Stats AS s
INNER JOIN  (
    		SELECT		theIP,
    				visitID,
    				MAX(CASE theFirst WHEN 1 THEN theURL ELSE NULL END) AS theURL
    		FROM		#Stats
    		WHERE		theFirst IN (1, MaxPages)
    		GROUP BY	theIP,
    				visitID
    		HAVING		MIN(theURL) = MAX(theURL)
    	) AS d ON d.theIP = s.theIP
    		AND d.visitID = s.visitid
WHERE   	s.theURL = d.theURL

-- 1
SELECT  	Country_Code,
    	PageDate AS DayOnly,
    	COUNT(DISTINCT visitID) AS VisitCount,
    	COUNT(theURL) AS [PageCount],
    	MAX(MaxPages) AS MaxPagesPerVisit,
    	CAST(AVG(1.0E * MaxPages) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
    	MAX(theTime) AS MaxTimeOnSite,
    	AVG(theTime) AS AvgTimeOnSite,
    	SUM(isGoal) AS GoalCount,
    	CAST(100.0E * SUM(Bounce) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM    	#Stats
GROUP BY    Country_Code,
    	PageDate
ORDER BY    PageDate,
    	Country_Code

-- 2
SELECT  	Country_Code,
    	COUNT(DISTINCT visitID) AS VisitCount,
    	COUNT(theURL) AS [PageCount],
    	MAX(MaxPages) AS MaxPagesPerVisit,
    	CAST(AVG(1.0E * MaxPages) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
    	MAX(theTime) AS MaxTimeOnSite,
    	AVG(theTime) AS AvgTimeOnSite,
    	SUM(isGoal) AS GoalCount,
    	CAST(100.0E * SUM(Bounce) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM    	#Stats
GROUP BY    Country_Code
ORDER BY    Country_Code

-- 3
SELECT  	theURL AS uriStem,
    	pageDate AS PageDay,
    	COUNT(*) AS [PageCount],
    	CAST(100.0E * SUM(CASE WHEN theFirst = 1 THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(12, 2)) AS EntryRate,
    	CAST(100.0E * SUM(CASE WHEN theFirst = MaxPages THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(12, 2)) AS ExitRate,
    	CAST(100.0E * SUM(Bounce) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM    	#Stats
GROUP BY    theURL,
    	PageDate
ORDER BY    PageDate,
    	theURL

DROP TABLE  #Stats
24 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso, nice job as always...are we allowed to update a table in the setup by adding columns and not have it count towards the execution time?
0 Likes 0 ·
@Scot - I need to ask Phil to clarify the rules around that!
0 Likes 0 ·
My feeling is that, ingenious though it is, Peso is bending the rules a little bit by doing so much computation in the set up section. The real-life example would include the import of the logs into the file, but we decided it was a bit too dull an activity to include in the competition. However, the time to load will be slower by the same amount as is gained by doing the computed columns. It will certainly shave a bit off his execution time. for this competition, I think we'll just carry on, because the rest of us can work the same tricks.
0 Likes 0 ·
I rather like experimenting with computed columns so it is nice to see it in the competition and it is part of the spirit of 'anything goes'. Would it be possible to run a timing with, and without the process of adding the two computed columns? I think it is a good idea in principle not to time the setup, but we're probably going to have to tighten up our definition of what is setup and what isn't for the next competition.
0 Likes 0 ·
The harness doesn't support adding timings for setup at the moment - and I probably won't have time to alter that because I'm on a contract with 7am starts... but we certainly could make two versions, one where that code is in the setup and one where it isn't...
0 Likes 0 ·
Show more comments
Phil Factor avatar image
Phil Factor answered
/*
    Phil1a --runs in 3.5 secs on my machine not including set up and tear down. 
    Sorry, didn't spot the code for the goal stuff but as it seemed to be all
    zeros in Reggie's results I just put a zero in. i'll add it for the next try
    DECLARE @timer TABLE
  (
   Time_ID INT,
   whatWasIt VARCHAR(30),
   whenWasIt DATETIME DEFAULT GETDATE()
  )*/

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountrySummary%' ) 
  DROP TABLE #CountrySummary
CREATE TABLE #CountrySummary
  (
   CountryCode [varchar](2),
   DayOnly [datetime],
   VisitCount [int],
   [PageCount] [int],
   MaxPagesPerVisit [int],
   AvgPagesPerVisit [decimal](12, 2),
   MaxTimeOnSite [int],
   AvgTimeOnSite [int],
   GoalCount [int],
   BounceRate [decimal](12, 2)
  )

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountryOverallSummary%' ) 
  DROP TABLE #CountryOverallSummary
CREATE TABLE #CountryOverallSummary
  (
   CountryCode [varchar](2),
   VisitCount [int],
   [PageCount] [int],
   MaxPagesPerVisit [int],
   AvgPagesPerVisit [decimal](12, 2),
   MaxTimeOnSite [int],
   AvgTimeOnSite [int],
   GoalCount [int],
   BounceRate [decimal](12, 2)
  )

----this is the main table used for processing the relevant log entries
IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#session%' ) 
  DROP TABLE #session
CREATE TABLE #session
  (
   Surrogate INT IDENTITY(1, 1),
   TheDateTime DATETIME,
   URIStem VARCHAR(20),
   TheIP VARCHAR(15),
   session INT DEFAULT 0,
   firstdot INT,
   Seconddot INT,
   Thirddot INT,
   BigIP BIGINT,
   CountryCode VARCHAR(2),
   IsLastHit INT DEFAULT 0,
   IsFirstHit INT
  )

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#countries%' ) 
  DROP TABLE #countries
CREATE TABLE #Countries
  (
   surrogate_id INT IDENTITY(1, 1),
   Previous_IP BIGINT DEFAULT 0,
   IP_start BIGINT CONSTRAINT PK_Goal PRIMARY KEY CLUSTERED,
   countryCode VARCHAR(2)
  )

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#aggregate%' ) 
  DROP TABLE #aggregate
CREATE TABLE #aggregate
  (
   [Pagecount] INT,
   [day] DATETIME,
   TheIP VARCHAR(15),
   Session INT,
   LastHit DATETIME,
   FirstHit DATETIME,
   CountryCode VARCHAR(2)
  )

-- INSERT  INTO @timer (WhatWasIt) SELECT  'start'

DECLARE @SessionTimeout INT, --how many seconds to assume new session
  @Session INT, --session number
  @LastDateTime DATETIME, --the date/time of the previous hit
  @ThePreviousIP VARCHAR(15),--the IP of the previous hit
  @firstdot INT, --the place in the IP string of the first dot
  @Seconddot INT,--the place in the IP string of the Second dot
  @Thirddot INT,--the place in the IP string of the third dot
  @bigIP BIGINT,--the int rendering of an IP address
  @IsFirstHit INT


SELECT  @SessionTimeout = 900, @Session = 0,
		@LastDateTime = '1 jan 2000', @ThePreviousIP = ''
--firstly, we get the relevant parts of the log table in a lightweight 
--temporary table
INSERT  INTO #session
		(TheDateTime, TheIP, URIStem)
		SELECT  TheDateTime, TheIP, csuriStem
		FROM    (SELECT CONVERT(DATETIME, datestr + ' ' + Timestr, 120)
						AS TheDateTime,
						LEFT(cip, 15) AS TheIP, csuriStem
				 FROM   iisaudit
				 WHERE  RIGHT(csuriStem, 5) IN ('.aspx',
						'.html') OR RIGHT(csuriStem, 4) IN (
						'.asp', '.htm', '.php')
				) f
		ORDER BY TheIP, TheDateTime
--and work out which seesion everything belongs to. This code relies 
--on a Quirky update

UPDATE  #session
SET     @Session = session = CASE WHEN TheIP <> @ThePreviousIP OR DATEDIFF(Second, @LastDateTime, TheDateTime) > @SessionTimeout
								  THEN @Session + 1
								  ELSE @Session
							 END,
		@isFirstHit = isFirstHit = CASE WHEN TheIP <> @ThePreviousIP OR DATEDIFF(Second, @LastDateTime, TheDateTime) > @SessionTimeout
										THEN 1
										ELSE 0
								   END,
		@LastDateTime = TheDateTime,
		@ThePreviousIP = TheIP,
		@firstdot = firstdot = CHARINDEX('.', TheIP, 1),
		@Seconddot = Seconddot = CHARINDEX('.', TheIP,
										   @firstdot + 1),
		@Thirddot = Thirddot = CHARINDEX('.', TheIP,
										 @SecondDot + 1),
		@BigIP = BigIP = CONVERT(BIGINT, 16777216) * (LEFT(TheIP, @FirstDot - 1)) + CONVERT(BIGINT, 65536) * (SUBSTRING(TheIP, @FirstDot + 1, @SecondDot - @FirstDot - 1)) + CONVERT(BIGINT, 256) * (SUBSTRING(TheIP, @SecondDot + 1, @ThirdDot - @SecondDot - 1)) + CONVERT(BIGINT, SUBSTRING(TheIP, @ThirdDot + 1, 20))

-- To lookup the geolocation data for an IP A.B.C.D, you need to find the row in [dbo].[ip_group_country] 
-- that has the highest [ip_start] value that is less than or equal to:
-- (A * 16777216) + (B * 65536) + (C * 256) + D
-- INSERT  INTO @timer (WhatWasIt) SELECT  'start loading countries'
--now we stock a country table in order to work out where the IP is from

INSERT  INTO #Countries
		(IP_Start, CountryCode)
		SELECT  ip_Start, Country_Code
		FROM    ip_group_country
		ORDER BY ip_start
--now we chamelessly do a quirky update to get the
-- bigint version of the previous IP address
-- INSERT  INTO @timer (WhatWasIt) SELECT  'update countries'

SELECT  @BigIP = 0
UPDATE  #countries
SET     @BigIP = Previous_IP = @BigIP,
		@BigIP = IP_Start

--create index IPStartPreviousIndex on #countries(Previous_IP,IP_Start)--
--create index BigIP_CoverTheDateTime on #Session(BigIP,TheDateTime)

--we only want to do this once as it is costly so we put
--it in a temporary table
-- INSERT  INTO @timer (WhatWasIt)  SELECT  'do the aggregate'

INSERT  INTO #aggregate
		SELECT  COUNT(*),
				DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0)
				AS day, TheIP, session, MAX(TheDateTime),
				MIN(TheDateTime), c.countryCode
		FROM    #session s INNER JOIN #countries c
				  ON BigIP BETWEEN Previous_IP AND IP_Start
		GROUP BY TheIP, session,
				DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0),
				c.countryCode

-- INSERT  INTO @timer (WhatWasIt) SELECT  'find last hits'
UPDATE  #session
SET     islasthit = 1
FROM    #session s INNER JOIN #aggregate
		  ON lastHit = theDateTime
-- INSERT  INTO @timer (WhatWasIt) SELECT  'report aggregates'
--and now we create the result tables.

INSERT  INTO [#CountrySummary]
		([CountryCode], [DayOnly], [VisitCount], [PageCount],
		 [MaxPagesPerVisit], [AvgPagesPerVisit],
		 [MaxTimeOnSite], [AvgTimeOnSite], goalcount,
		 [BounceRate])
		SELECT  countryCode, day, COUNT(*), SUM([PageCount]),
				MAX([PageCount]), AVG([PageCount] * 1.0),
				MAX(DATEDIFF(s, [FirstHit], [LastHit])),
				AVG(DATEDIFF(s, [FirstHit], [LastHit])), 0,
				SUM(CASE WHEN [PageCount] = 1 THEN 1
						 ELSE 0
					END) * 100.0 / (COUNT(*) * 1.0)
		FROM    #aggregate
		GROUP BY [CountryCode], day

INSERT  INTO [#CountryOverallSummary]
		([CountryCode], [VisitCount], [PageCount],
		 [MaxPagesPerVisit], [AvgPagesPerVisit],
		 [MaxTimeOnSite], [AvgTimeOnSite], [GoalCount],
		 [BounceRate])
		SELECT  countryCode, COUNT(*), SUM([PageCount]),
				MAX([PageCount]), AVG([PageCount] * 1.0),
				MAX(DATEDIFF(s, [FirstHit], [LastHit])),
				AVG(DATEDIFF(s, [FirstHit], [LastHit])), 0,
				SUM(CASE WHEN [PageCount] = 1 THEN 1
						 ELSE 0
					END) * 100.0 / (COUNT(*) * 1.0)
		FROM    #aggregate
		GROUP BY [CountryCode] 

Select * from [#CountryOverallSummary]

Select * from [#CountrySummary]

SELECT  uriStem,
		DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0) AS PageDay,
		COUNT(*) AS pagecount,
		CONVERT([decimal](12, 2), SUM(isFirstHit) * 100.0 / COUNT(*))
		AS [EntryRate],
		CONVERT([decimal](12, 2), SUM(isLastHit) * 100.0 / COUNT(*))
		AS [ExitRate],
		CONVERT([decimal](12, 2), SUM(CASE WHEN isFirstHit + isLastHit = 2
										   THEN 1
										   ELSE 0
									  END) * 100.0 / COUNT(*))
		AS [BounceRate]
FROM    #session
GROUP BY uriStem,
		DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0)
ORDER BY PageDay, uristem

/*-- INSERT  INTO @timer  (WhatWasIt)  SELECT  'end'
SELECT  * FROM    @timer*/

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountrySummary%' ) 
  DROP TABLE #CountrySummary

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountryOverallSummary%' ) 
  DROP TABLE #CountryOverallSummary

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#session%' ) 
  DROP TABLE #session

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#countries%' ) 
  DROP TABLE #countries

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#aggregate%' ) 
  DROP TABLE #aggregate
12 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This runs in about 5 seconds on my machine which is hugely impressive. The Goal URL score could be put in the aggregation stage so it wouldn't be too much of an additional overhead.
0 Likes 0 ·
Thanks for that. I haven't done any work in optimizing it yet so there is a potential for shaving quite a bit off the timings. Last time I checked, the main report was exactly the same as Reggie's but the country reports were quite different.
0 Likes 0 ·
Phil - do you want me to split this into setup, main & teardown and add deletes from the temporary table to the beginning of main? Also with the results - could well be the ordering issue, because there are multiple page requests from the same IP in the same second, which idiot me didn't notice previously... I thought of adding an IDENTITY to the set after bulk loading - what do you think?
0 Likes 0 ·
As this is the first cut, I'd say do the timings on the code as it is if is less trouble. We only want rough timings at this stage. I'd say leave the import table as is now. I reckon it is good practice to take data in as near as dammit to what it was like when it was in the import file. It is then part of the task to cope with things like this. This is why I put in the surrogate key into the session table. The problem is finding a primary key that will actually add value. It doesn't help to use the surrogate, of course.
0 Likes 0 ·
I did the algorithm to find the source of the IP address using excel and even a pocket calculator, and I still get different results to Reggie, so I must have got my understanding of the process entirely wrong.
0 Likes 0 ·
Show more comments
Phil Factor avatar image
Phil Factor answered
/*Phil1b --runs in 2.5 secs on my machine not including set up and tear down. 
Again forgot the code for the goal stuff. It is a lot more accurate this time, and
where I've checked it against pesos and Reggie's code, mine is more accurate than 
either.

DECLARE @timer    TABLE
  (
   Time_ID INT IDENTITY(1, 1),
   whatWasIt VARCHAR(30),
   whenWasIt DATETIME DEFAULT GETDATE()
  ) */

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountrySummary%' ) 
  DROP TABLE #CountrySummary
CREATE TABLE #CountrySummary
  (
   CountryCode [varchar](2),
   DayOnly [datetime],
   VisitCount [int],
   [PageCount] [int],
   MaxPagesPerVisit [int],
   AvgPagesPerVisit [decimal](12, 2),
   MaxTimeOnSite [int],
   AvgTimeOnSite [int],
   GoalCount [int],
   BounceRate [decimal](12, 2)
  )

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountryOverallSummary%' ) 
  DROP TABLE #CountryOverallSummary
CREATE TABLE #CountryOverallSummary
  (
   CountryCode [varchar](2),
   VisitCount [int],
   [PageCount] [int],
   MaxPagesPerVisit [int],
   AvgPagesPerVisit [decimal](12, 2),
   MaxTimeOnSite [int],
   AvgTimeOnSite [int],
   GoalCount [int],
   BounceRate [decimal](12, 2)
  )

----this is the main table used for processing the relevant log entries
IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#session%' ) 
  DROP TABLE #session
/*CREATE TABLE #session
  (
   Surrogate INT IDENTITY(1, 1),
   TheDay int,
   TheDateTime DATETIME,
   URIStem VARCHAR(20),
   TheIP VARCHAR(15),
   session INT DEFAULT 0,
   firstdot INT,
   Seconddot INT,
   Thirddot INT,
   BigIP BIGINT,
   IsLastHit INT DEFAULT 0,
   IsFirstHit INT
  )*/

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#countries%' ) 
  DROP TABLE #countries
CREATE TABLE #Countries
  (
   surrogate_id INT IDENTITY(1, 1),
   Next_IP BIGINT DEFAULT 0,
   IP_start BIGINT,
   countryCode VARCHAR(2),
   CONSTRAINT idxClusterd PRIMARY KEY (IP_Start DESC)
  )


IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#aggregate%' ) 
  DROP TABLE #aggregate
CREATE TABLE #aggregate
  (
   [Pagecount] INT,
   [day] INT,
   TheIP VARCHAR(15),
   bigIP BIGINT,
   Session INT,
   LastHit DATETIME,
   FirstHit DATETIME,
   LastSessionID INT,
   CountryCode VARCHAR(2)
  )

-- INSERT  INTO @timer (WhatWasIt) SELECT  'start'

DECLARE @SessionTimeout INT, --how many seconds to assume new session
  @Session INT, --session number
  @LastDateTime DATETIME, --the date/time of the previous hit
  @ThePreviousIP VARCHAR(15),--the IP of the previous hit
  @firstdot INT, --the place in the IP string of the first dot
  @Seconddot INT,--the place in the IP string of the Second dot
  @Thirddot INT,--the place in the IP string of the third dot
  @bigIP BIGINT,--the int rendering of an IP address
  @IsFirstHit INT,
  @NextCountry VARCHAR(2)


SELECT  @SessionTimeout = 900, @Session = 0,
		@LastDateTime = '1 jan 2000', @ThePreviousIP = ''
--firstly, we get the relevant parts of the log table in a lightweight 
--temporary table
SELECT  IDENTITY( INT,1, 1 ) AS Session_ID,
		CONVERT(INT, 0) AS TheDay, TheDateTime,
		CONVERT (VARCHAR(20), csURIStem) AS URIStem,
		CONVERT(VARCHAR(15), TheIP) AS TheIP, 0 AS session,
		0 AS firstdot, 0 AS Seconddot, 0 AS Thirddot,
		CONVERT(BIGINT, 0) AS BigIP, 0 AS IsLastHit,
		0 AS IsFirstHit,
		CONVERT(VARCHAR(2), ' ') AS countrycode
INTO    #session
FROM    (SELECT CONVERT(DATETIME, datestr + ' ' + Timestr, 120)
				AS TheDateTime, LEFT(cip, 15) AS TheIP,
				csuriStem
		 FROM   iisaudit
		 WHERE  RIGHT(csuriStem, 5) IN ('.aspx', '.html') OR RIGHT(csuriStem, 4) IN (
				'.asp', '.htm', '.php')
		) f
ORDER BY TheIP, TheDateTime
--and work out which seesion everything belongs to. This code relies 
--on a Quirky update

UPDATE  #session
SET     @Session = session = CASE WHEN TheIP <> @ThePreviousIP OR DATEDIFF(Second, @LastDateTime, TheDateTime) > @SessionTimeout
								  THEN @Session + 1
								  ELSE @Session
							 END,
		TheDay = DATEDIFF(d, 0, [TheDateTime]),
		@isFirstHit = isFirstHit = CASE WHEN TheIP <> @ThePreviousIP OR DATEDIFF(Second, @LastDateTime, TheDateTime) > @SessionTimeout
										THEN 1
										ELSE 0
								   END,
		@LastDateTime = TheDateTime,
		@ThePreviousIP = TheIP,
		@firstdot = firstdot = CHARINDEX('.', TheIP, 1),
		@Seconddot = Seconddot = CHARINDEX('.', TheIP,
										   @firstdot + 1),
		@Thirddot = Thirddot = CHARINDEX('.', TheIP,
										 @SecondDot + 1),
		@BigIP = BigIP = CONVERT(BIGINT, 16777216) * (LEFT(TheIP, @FirstDot - 1)) + CONVERT(BIGINT, 65536) * (SUBSTRING(TheIP, @FirstDot + 1, @SecondDot - @FirstDot - 1)) + CONVERT(BIGINT, 256) * (SUBSTRING(TheIP, @SecondDot + 1, @ThirdDot - @SecondDot - 1)) + CONVERT(BIGINT, SUBSTRING(TheIP, @ThirdDot + 1, 20))

-- To lookup the geolocation data for an IP A.B.C.D, you need to find the row in [dbo].[ip_group_country] 
-- that has the highest [ip_start] value that is less than or equal to:
-- (A * 16777216) + (B * 65536) + (C * 256) + D
-- INSERT  INTO @timer (WhatWasIt) SELECT  'start loading countries'
--now we stock a country table in order to work out where the IP is from

INSERT  INTO #Countries
		(IP_Start, CountryCode)
		SELECT  ip_Start, Country_Code
		FROM    ip_group_country

--now we chamelessly do a quirky update to get the
-- bigint version of the previous IP address
-- INSERT  INTO @timer (WhatWasIt) SELECT  'update countries'

SELECT  @BigIP = 0, @NextCountry = ''
UPDATE  #countries
SET     @BigIP = Next_IP = @BigIP - 1,
		@BigIP = IP_Start

--we only want to do this once as it is costly so we put
--it in a temporary table
-- INSERT  INTO @timer (WhatWasIt)  SELECT  'do the aggregate'
INSERT  INTO #aggregate
		SELECT  COUNT(*), Theday, MAX(TheIP), MAX(BigIP),
				session, MAX(TheDateTime), MIN(TheDateTime),
				MAX(Session_ID), 0--, countryCode
		FROM    #session
		GROUP BY session, TheDay
--, countryCode
--now do the countrycode
UPDATE  #aggregate
SET     countrycode = c.countrycode
FROM    #aggregate s INNER JOIN #countries c
		  ON BigIP BETWEEN IP_Start AND Next_IP

-- INSERT  INTO @timer (WhatWasIt) SELECT  'find last hits'
UPDATE  #session
SET     islasthit = 1
FROM    #session s INNER JOIN #aggregate
		  ON session_ID = LastSessionID
-- INSERT  INTO @timer (WhatWasIt) SELECT  'report aggregates'
--and now we create the result tables.

/*INSERT  INTO [#CountrySummary]
		([CountryCode], [DayOnly], [VisitCount], [PageCount],
		 [MaxPagesPerVisit], [AvgPagesPerVisit],
		 [MaxTimeOnSite], [AvgTimeOnSite], goalcount,
		 [BounceRate])*/
SELECT  countryCode, DATEADD(d, day, 0), COUNT(*),
		SUM([PageCount]), MAX([PageCount]),
		AVG([PageCount] * 1.0),
		MAX(DATEDIFF(s, [FirstHit], [LastHit])),
		AVG(DATEDIFF(s, [FirstHit], [LastHit])), 0,
		SUM(CASE WHEN [PageCount] = 1 THEN 1
				 ELSE 0
			END) * 100.0 / (COUNT(*) * 1.0)
FROM    #aggregate
GROUP BY [CountryCode], day
ORDER BY countrycode, day

/*INSERT  INTO [#CountryOverallSummary]
		([CountryCode], [VisitCount], [PageCount],
		 [MaxPagesPerVisit], [AvgPagesPerVisit],
		 [MaxTimeOnSite], [AvgTimeOnSite], [GoalCount],
		 [BounceRate])*/
SELECT  countryCode, COUNT(*), SUM([PageCount]),
		MAX([PageCount]), AVG([PageCount] * 1.0),
		MAX(DATEDIFF(s, [FirstHit], [LastHit])),
		AVG(DATEDIFF(s, [FirstHit], [LastHit])), 0,
		SUM(CASE WHEN [PageCount] = 1 THEN 1
				 ELSE 0
			END) * 100.0 / (COUNT(*) * 1.0)
FROM    #aggregate
GROUP BY [CountryCode]
ORDER BY countrycode


--Select * from [#CountrySummary]

SELECT  uriStem,
		DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0) AS PageDay,
		COUNT(*) AS pagecount,
		CONVERT([decimal](12, 2), SUM(isFirstHit) * 100.0 / COUNT(*))
		AS [EntryRate],
		CONVERT([decimal](12, 2), SUM(isLastHit) * 100.0 / COUNT(*))
		AS [ExitRate],
		CONVERT([decimal](12, 2), SUM(CASE WHEN isFirstHit + isLastHit = 2
										   THEN 1
										   ELSE 0
									  END) * 100.0 / COUNT(*))
		AS [BounceRate]
FROM    #session
GROUP BY uriStem,
		DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0)
ORDER BY PageDay, uristem


-- INSERT  INTO @timer  (WhatWasIt)  SELECT  'end'
/*SELECT  t.WhatWasIt, DATEDIFF(ms, t.WhenWasIt, y.WhenWasIt)
FROM    @timer t INNER JOIN @timer y
		  ON y.time_ID = t.time_ID + 1
UNION ALL
SELECT  'total',
		DATEDIFF(ms,
				 (SELECT WhenWasIt FROM @timer WHERE WhatWasIt
						  = 'start'
				 ),
				 (SELECT WhenWasIt FROM @timer WHERE WhatWasIt
						  = 'end'
				 )) 
    */

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountrySummary%' ) 
  DROP TABLE #CountrySummary

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountryOverallSummary%' ) 
  DROP TABLE #CountryOverallSummary

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#session%' ) 
  DROP TABLE #session

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#countries%' ) 
  DROP TABLE #countries

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#aggregate%' ) 
  DROP TABLE #aggregate
9 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is definitely closer - There's a few foibles now (for example, country 'BA' in the country summary shows 9 visits in your results, and it should be 8), but definitely much closer! Also the ordering in your country by day summary should be day, country and not country, day... But this is definitely the closest :)
0 Likes 0 ·
But is Phil allowed to enter his own competition? ;-p
0 Likes 0 ·
Of course! :) At the rate he's going, he's going to be the winner!
0 Likes 0 ·
What is the correct ORDER BY clause for Reggies code, so we can compare our results? I get parallellism all over the places so it's hard to know.
0 Likes 0 ·
C'mon you others. I'm feeling lonely. Isn't anyone else going to enter the competition?
0 Likes 0 ·
Show more comments
Matt Whitfield avatar image
Matt Whitfield answered

Ok, well this isn't an entry, it's just me doing my bit to persuade the people who don't believe there is a place for the CLR in SQL Server that maybe they should re-consider. I used Peso's PERSISTED computed column with the extensions, and another one to pre-cache the ip_start lookup value. This runs in 314ms on my box.

Setup:

ALTER TABLE [dbo].[IISAudit] ADD [id] [int] IDENTITY (1, 1) NOT NULL
GO

CREATE ASSEMBLY [SqlClassLibrary]
AUTHORIZATION [dbo]
FROM
0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030009B2814B0000000000000000E00002210B0108000026000000060000000000006E45000000200000006000000000400000200000000200000400000000000000040000000000000000A0000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001C4500004F000000006000008003000000000000000000000000000000000000008000000C000000844400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000074250000002000000026000000020000000000000000000000000000200000602E7273726300000080030000006000000004000000280000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000002C0000000000000000000000000000400000420000000000000000000000000000000050450000000000004800000002000500E82F00009C140000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300400460000000100001102280E00000A0000020E047D07000004020203250A7D01000004067D02000004020204250B7D03000004077D0400000402177D0500000402052D03162B01177D06000004002A00001330030036000000020000110002257B0500000417587D050000040516FE010A062D100002257B0600000417587D060000040002037D0200000402047D040000042A000013300200360000000200001102280E00000A000002037D09000004020F02280F00000A7D0A00000402177D0B0000040516FE010A062D090002177D0C00000400002A00001330030028000000020000110002257B0B00000417587D0B0000040316FE010A062D100002257B0C00000417587D0C000004002A133002003F00000003000011000374030000020A027C0A000004067B0A000004281000000A0B0716FE010D092D0500070C2B16027B09000004067B090000046F1100000A0B070C2B00082A00133004008B0000000400001102280E00000A000002037B080000047D0F00000402037C01000004280F00000A7D1000000402177D110000040202037B05000004250A7D12000004067D130000040202037B02000004037B01000004281200000A0B1201281300000A69250A7D14000004067D1500000402037B060000047D1600000402037B05000004172E03162B01177D17000004002A0013300400B5000000050000110002257B1100000417587D1100000402257B12000004037B05000004587D12000004037B05000004027B13000004FE0216FE010B072D0E0002037B050000047D1300000400037B02000004037B01000004281200000A0C1202281300000A690A02257B1400000406587D1400000406027B15000004FE0216FE010B072D090002067D150000040002257B16000004037B06000004587D1600000402257B17000004037B05000004172E03162B0117587D170000042A000000133002003F00000006000011000374040000020A027C10000004067B10000004281000000A0B0716FE010D092D0500070C2B16027B0F000004067B0F0000046F1100000A0B070C2B00082A00133004007A0000000400001102280E00000A000002037B080000047D1800000402177D190000040202037B05000004250A7D1A000004067D1B0000040202037B02000004037B01000004281200000A0B1201281300000A69250A7D1C000004067D1D00000402037B060000047D1E00000402037B05000004172E03162B01177D1F000004002A000013300400B5000000050000110002257B1900000417587D1900000402257B1A000004037B05000004587D1A000004037B05000004027B1B000004FE0216FE010B072D0E0002037B050000047D1B00000400037B02000004037B01000004281200000A0C1202281300000A690A02257B1C00000406587D1C00000406027B1D000004FE0216FE010B072D090002067D1D0000040002257B1E000004037B06000004587D1E00000402257B1F000004037B05000004172E03162B0117587D1F0000042A000000133002001E00000007000011000374050000020A027B18000004067B180000046F1100000A0B2B00072A00001B300900F909000008000011007201000070731500000A0A00066F1600000A00731700000A0B723100007006731800000A0C00086F1900000A0D002B10000709166F1A00000A6F1B00000A0000096F1C00000A131F111F2DE400DE120914FE01131F111F2D07096F1D00000A00DC0000DE120814FE01131F111F2D07086F1D00000A00DC0020A00F0000731E00000A1304727900007006731800000A0C00086F1900000A0D002B1800110409166F1F00000A09176F1A00000A6F2000000A0000096F1C00000A131F111F2DDC00DE120914FE01131F111F2D07096F1D00000A00DC0000DE120814FE01131F111F2D07086F1D00000A00DC00732100000A1305732200000A1306732300000A1307732400000A130872F401007006731800000A0C00086F1900000A0D0038220100000009186F1F00000A130909176F1A00000A130A09166F2500000A130B07110A6F2600000A130C16130D11061109120E6F2700000A16FE01131F111F2D5E00110E7C02000004026C282800000A110B282900000A16FE01131F111F2D1200110E110B110A110C6F0200000600002B2A00110B110A110C11097301000006130E11061109110E6F2A00000A001105110E6F2B00000A0017130D00002B2A00110B110A110C11097301000006130E11061109110E6F2C00000A001105110E6F2B00000A0017130D00110A120B721B030070282D00000A282E00000A130F1107110F12106F2F00000A16FE01131F111F2D0E001110110D6F0400000600002B2500110A110B110D730300000613101107110F11106F3000000A00110811106F3100000A000000096F1C00000A131F111F3ACFFEFFFF00DE120914FE01131F111F2D07096F1D00000A00DC0000DE120814FE01131F111F2D07086F1D00000A00DC00733200000A1311733300000A1312733400000A1313733500000A13140011056F3600000A1320384E0100001220283700000A130E00110E1104110E7B070000046F3800000A7D08000004110E7B04000004110E7C01000004721B030070282D00000A282E00000A1315110E7B08000004110E7C01000004721B030070282D00000A282E00000A13161114110E7B0800000412176F3900000A16FE01131F111F2D0E001117110E6F0A00000600002B2600110E730900000613171114110E7B0800000411176F3A00000A00111211176F3B00000A00001113111612186F3C00000A16FE01131F111F2D0E001118110E6F0700000600002B2100110E730600000613181113111611186F3D00000A00111111186F3E00000A00001107111512106F2F00000A16FE01131F111F2D40001110257B0D00000417587D0D000004110E7B03000004110E7B04000004283F00000A16FE01131F111F2D11001110257B0E00000417587D0E00000400002B0C007229030070734000000A7A001220284100000A131F111F3AA2FEFFFFDE0F1220FE160B00001B6F1D00000A00DC0011086F4200000A0011116F4300000A0011126F4400000A001F0A8D1F000001132111211672310300701F16186A734500000AA211211772490300701A734600000AA211211872590300701E734600000AA2112119726F0300701E734600000AA211211A72830300701E734600000AA211211B72A50300701B1F0C18734700000AA211211C72C70300701E734600000AA211211D72E30300701E734600000AA211211E72FF0300701E734600000AA211211F0972130400701B1F0C18734700000AA21121734800000A1319284900000A11196F4A00000A000011116F4B00000A1322380D0100001222284C00000A13180011191611187B0F0000046F4D00000A0011191711187B100000046F4E00000A0011187B12000004131A11187B11000004131B111918111B6F4F00000A00111919111A6F4F00000A0011191A11187B130000046F4F00000A0011191B111A285000000A17735100000A285200000A111B285000000A285300000A1817285400000A6F5500000A0011191C11187B150000046F4F00000A0011191D11187B14000004111B5B6F4F00000A0011191E11187B160000046F4F00000A0011191F0911187B17000004285000000A20E803000016161617735600000A285200000A111B285000000A285300000A1817285400000A6F5500000A00284900000A11196F5700000A00001222285800000A131F111F3AE3FEFFFFDE0F1222FE160C00001B6F1D00000A00DC00284900000A6F5900000A001F098D1F000001132111211672310300701F16186A734500000AA211211772590300701E734600000AA2112118726F0300701E734600000AA211211972830300701E734600000AA211211A72A50300701B1F0C18734700000AA211211B72C70300701E734600000AA211211C72E30300701E734600000AA211211D72FF0300701E734600000AA211211E72130400701B1F0C18734700000AA21121734800000A131C284900000A111C6F4A00000A000011126F5A00000A132338FC0000001223285B00000A131D00111C16111D7B180000046F4D00000A00111D7B1A000004131A111D7B19000004131B111C17111B6F4F00000A00111C18111A6F4F00000A00111C19111D7B1B0000046F4F00000A00111C1A111A285000000A17735100000A285200000A111B285000000A285300000A1817285400000A6F5500000A00111C1B111D7B1D0000046F4F00000A00111C1C111D7B1C000004111B5B6F4F00000A00111C1D111D7B1E0000046F4F00000A00111C1E111D7B1F000004285000000A20E803000016161617735600000A285200000A111B285000000A285300000A1817285400000A6F5500000A00284900000A111C6F5700000A00001223285C00000A131F111F3AF4FEFFFFDE0F1223FE160D00001B6F1D00000A00DC00284900000A6F5900000A001C8D1F000001132111211672290400701F1620FF0000006A734500000AA211211772390400701A734600000AA2112118726F0300701E734600000AA211211972490400701B1F0C18734700000AA211211A725D0400701B1F0C18734700000AA211211B72130400701B1F0C18734700000AA21121734800000A131E284900000A111E6F4A00000A000011086F5D00000A132438FD0000001224285E00000A131000111E1611107B090000046F4D00000A00111E1711107B0A0000046F4E00000A0011107B0B000004131A111E18111A6F4F00000A00111E1911107B0C000004285000000A20E803000016161617735600000A285200000A111A285000000A285300000A1817285400000A6F5500000A00111E1A11107B0D000004285000000A20E803000016161617735600000A285200000A111A285000000A285300000A1817285400000A6F5500000A00111E1B11107B0E000004285000000A20E803000016161617735600000A285200000A111A285000000A285300000A1817285400000A6F5500000A00284900000A111E6F5700000A00001224285F00000A131F111F3AF3FEFFFFDE0F1224FE160E00001B6F1D00000A00DC00284900000A6F5900000A0000DE120614FE01131F111F2D07066F1D00000A00DC002A000000410C0100020000002E0000002200000050000000120000000000000002000000260000004000000066000000120000000000000002000000990000002A000000C30000001200000000000000020000009100000048000000D90000001200000000000000020000001C0100003A0100005602000012000000000000000200000014010000580100006C020000120000000000000002000000A5020000650100000A0400000F0000000000000002000000FB040000240100001F0600000F0000000000000002000000F306000013010000060800000F0000000000000002000000B308000014010000C70900000F00000000000000020000000C000000D9090000E509000012000000000000001E02280E00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000006C060000237E0000D80600006406000023537472696E6773000000003C0D00007004000023555300AC110000100000002347554944000000BC110000E002000023426C6F620000000000000002000001571702080900000000FA0133001600000100000026000000060000001F0000000D00000013000000030000005F0000000C000000080000000E000000010000000200000000000A0001000000000006006900620006007000620006007C0062000600CB01B9010600E201B9010600FF01B90106001E02B90106003702B90106005002B90106006B02B90106008602B9010600BE029F020600D202B9010600FE02EB023B001203000006004103210306006103210306009803620006009F0362000A00EF03D4030A001B0405040A003C042904060069044E040A00700405040A007B0405040A00970429040600B30462000600C7044E045F003105000006006B0562000A008305D4030A008F05C8030A009905D4030A00A705D4030A00B205D4030600F305620006001F0662000600240662000000000001000000000001000100000110001E0000000500010001000001100024000000050009000300000110002D00000005000F000600000110003C0000000500180009000100100048000000050020000C00060085000A00060093000A000600A0000E000600AA000E000600B30011000600BD0011000600C70014000600CD000E000600E7000E000600F0000A000600B30011000600F40011000600FF0011000600090111000600CD000E000600F0000A000600230111000600B300110006002E01110006003B01110006004B0111000600BD0011000600090111000600CD000E000600230111000600B300110006002E01110006003B01110006004B0111000600BD0011000600090111005020000000008618D90017000100A420000000008600DF0020000500E820000000008618D900280008002C21000000008600150130000B00602100000000E601190135000C00AC21000000008618D9003A000D00442200000000860015013A000E00082300000000E601190135000F005423000000008618D9003A001000DC2300000000860015013A001100A02400000000E601190135001200CC24000000009600590140001300E02F000000008618D90045001400000001006501000002006E01000003007801000004008301000001006501000002008901000003007801000001008E01000002009701000003009B01000001009B0100000100A40100000100A80100000100A80100000100A40100000100A80100000100A80100000100A40100000100AA010300090004000900050009002100D90049002900D90049003100D90049003900D90049004100D90049004900D90049005100D90049005900D90049006100D90030006900D90049007100D9004E008100D90054008900D90045000900D900450019008F036300190019016800910019016E001900A8037B009900B7038400A100D9004500A900D9004900B100490445000C00D9004500C100D900AE00C1008904B500D100A404BA000C001501BF00D100AE04C500D900BF0445001400D9005400D100D404D00014001501D5001C00D90045002400D90045002C00D90045003400D9004500D100DD04FB000C00E90401012400F20407011900FE04100119000905160124001805D5001C001501BF0024001501D500190021051E0191002A0523012C00F20407012C001501D50034001501BF003C00D90045004400D90045004C00D90045005400D90045001C003C0547015C004A055701140056055C015400F204070154001501D50044001501BF004C00F20407014C001501D5003C001501BF0091005F056301F100D90049005C007505C50034007E0545003C007E05450044007E054500F900D9006901F900D9007201F900D9007A010901D90084011101BA058B011901C30591013C003C05470164004A0557010901D4059F010901DE05A5010901EA05AC012101FB05B2012101D900540021010706B90121011306B90129013506C50109013B06D2012101D900DA0119014606910164007505C50019015506450044003C0547016C004A0557016C007505C50034003C05470174004A05570174007505C5002E002B0074022E000B0074022E0013008E022E001B008E022E0023008E022E004B008E022E006B00BE022E003B008E022E00330094022E005B00AC022E006300B5028001A300A30059005F00730088008E0095009D00F101A800C900DD00E400EC00F4002901300137013F0150019801E301EA010480000001000000780E449D0000000000007F03000002000000000000000000000001005900000000000200000000000000000000000100C8030000000000000000003C4D6F64756C653E0053716C436C6173734C6962726172792E646C6C00566973697400506167655374617400436F756E7472794461795374617400436F756E747279537461740053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A6563740049436F6D70617261626C65004461746554696D650046697273745061676554696D65004C6173745061676554696D6500466972737450616765004C617374506167650050616765436F756E7400476F616C436F756E7400495042696700436F756E747279436F6465002E63746F72004164645061676500506167654E616D650044617900456E747279436F756E740045786974436F756E7400426F756E6365436F756E740041646400436F6D70617265546F005669736974436F756E74004D617850616765436F756E7400546F74616C54696D654F6E53697465004D617854696D654F6E53697465004949534C6F675061727365007061676554696D650066697273745061676500706167654973476F616C006970426967007061676500706167654E616D650064617900776173456E747279006F626A00760053657373696F6E54696D656F75740053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C436C6173734C696272617279006765745F4461746500537472696E670054696D655370616E006F705F5375627472616374696F6E006765745F546F74616C5365636F6E64730053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053797374656D2E436F6C6C656374696F6E732E47656E65726963004C69737460310053716C436F6D6D616E640053716C4461746152656164657200457865637574655265616465720044624461746152656164657200476574537472696E6700526561640049446973706F7361626C6500446973706F73650044696374696F6E617279603200476574496E743634004765744461746554696D6500436F6E7461696E730054727947657456616C7565004164645365636F6E6473006F705F477265617465725468616E007365745F4974656D00546F537472696E6700436F6E63617400456E756D657261746F7200476574456E756D657261746F72006765745F43757272656E74006765745F4974656D006F705F457175616C69747900457863657074696F6E004D6F76654E65787400536F72740053716C4D657461446174610053716C4462547970650053716C446174615265636F72640053716C436F6E746578740053716C50697065006765745F506970650053656E64526573756C7473537461727400536574537472696E67005365744461746554696D6500536574496E74333200446563696D616C006F705F496D706C69636974006F705F4D756C7469706C79006F705F4469766973696F6E004D617468004D6964706F696E74526F756E64696E6700526F756E6400536574446563696D616C0053656E64526573756C7473526F770053656E64526573756C7473456E6400002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000047530045004C0045004300540020005B00630073005500720069005300740065006D005D002000660072006F006D0020005B00230067006F0061006C00550052004C0073005D00008179530045004C0045004300540020005B00490050004200690067005D002C0020002800530045004C00450043005400200054004F0050002000310020005B0063006F0075006E007400720079005F0063006F00640065005D002000460052004F004D0020005B00640062006F005D002E005B00690070005F00670072006F00750070005F0063006F0075006E007400720079005D0020005700480045005200450020005B00690070005F00730074006100720074005D0020003C0020005B00490050004200690067005D0020004F00520044004500520020004200590020005B00690070005F00730074006100720074005D002000640065007300630029002000460052004F004D0020005B00640062006F005D002E005B00490049005300410075006400690074005D0020005700480045005200450020005B00560061006C006900640045007800740032005D0020003D00200031002000470052004F005500500020004200590020005B00490050004200690067005D00008125530045004C00450043005400200043004F004E00560045005200540028005B006400610074006500740069006D0065005D002C0020006400610074006500730074007200290020002B00200043004F004E00560045005200540028005B006400610074006500740069006D0065005D002C002000740069006D00650073007400720029002C0020005B00630073005500720069005300740065006D005D002C0020005B00490050004200690067005D002000460052004F004D0020005B00640062006F005D002E005B00490049005300410075006400690074005D0020005700480045005200450020005B00560061006C006900640045007800740032005D0020003D002000310020004F00520044004500520020004200590020005B00690064005D00000D790079004D004D00640064000007570054004600001743006F0075006E0074007200790043006F0064006500000F4400610079004F006E006C00790000155600690073006900740043006F0075006E0074000013500061006700650043006F0075006E00740000214D0061007800500061006700650073005000650072005600690073006900740000214100760067005000610067006500730050006500720056006900730069007400001B4D0061007800540069006D0065004F006E005300690074006500001B410076006700540069006D0065004F006E005300690074006500001347006F0061006C0043006F0075006E007400001542006F0075006E00630065005200610074006500000F7500720069005300740065006D00000F5000610067006500440061007900001345006E0074007200790052006100740065000011450078006900740052006100740065000000E2AF83D8F07A4645B3B3735C905DDD3E0008B77A5C561934E0890306110D02060E02060802060A08200401110D0E020A07200301110D0E02072003010E110D020420010102042001081C052001011208040001010803200001042001010E05200101113D0420010108050702110D0E03070102042000110D05200108110D042001080E070704120C080802080002114D110D110D0320000D05070208114D0607030802114D070704121008080205070212140804010000000515125D010E062002010E125504200012650420010E080520010113000320000206151271020A0E0420010A0807200201130013010615125D01120807151271020A120807151271020E120C0615125D01120C052001110D08052001021300082002021300101301052001110D0D07000202110D110D0420010E0E0500020E0E0E0615125D0112100615125D01121407151271020E121007151271020E121408200015117501130006151175011208042000130006200113011300050002020E0E082003010E1180810A072002010E118081092004010E1180810505062001011D127D05000012808D062001011280850615117501121005200201080E0620020108110D052002010808060001118091080B00021180911180911180910C0003118091118091081180990720020108118091082005010808080205061511750112140615117501120C80810725125515125D010E12611265151271020A0E15125D011208151271020A1208151271020E120C15125D01120C0A0E110D020212080E120C15125D01121015125D011214151271020E1210151271020E12140E0E1214121012808508081280851214128085021511750112081D127D15117501121015117501121415117501120C190100145068696C466163746F724368616C6C656E676534000005010000000017010012436F7079726967687420C2A920203230313000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000000009B2814B00000000020000007B000000A0440000A026000052534453308BD673F80BA9408151F398821A7E1D0E000000433A5C436F64655C41746C616E7469735C5468726F77617761795C5068696C466163746F724368616C6C656E6765345C5068696C466163746F724368616C6C656E6765345C6F626A5C44656275675C53716C436C6173734C6962726172792E70646200004445000000000000000000005E45000000200000000000000000000000000000000000000000000050450000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058600000280300000000000000000000280334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100449D780E00000100449D780E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00488020000010053007400720069006E006700460069006C00650049006E0066006F000000640200000100300030003000300030003400620030000000540015000100460069006C0065004400650073006300720069007000740069006F006E00000000005000680069006C0046006100630074006F0072004300680061006C006C0065006E006700650034000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700300034002E00340030003200360030000000000048001400010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C006100730073004C006900620072006100720079002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C006100730073004C006900620072006100720079002E0064006C006C0000004C0015000100500072006F0064007500630074004E0061006D006500000000005000680069006C0046006100630074006F0072004300680061006C006C0065006E006700650034000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700300034002E00340030003200360030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700300034002E0034003000320036003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000703500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
ALTER ASSEMBLY [SqlClassLibrary]
WITH VISIBILITY = ON
GO

CREATE PROCEDURE [dbo].[IISLogParse]
 (@SessionTimeout int )
AS EXTERNAL NAME [SqlClassLibrary].[StoredProcedures].[IISLogParse]
GO

create function [dbo].[fn_CHARINDEX](@ch char(1), @s varchar(8000))
returns int with schemabinding as
begin
DECLARE @i int
set @i = 1
while @i <= len(@s) begin
if substring(@s,@i,1) = @ch
return @i
set @i = @i + 1
end
return NULL
end

GO

CREATE FUNCTION [dbo].[getIpBig](@ip [varchar](24))
  RETURNS [bigint]
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @part1 [int], @part2 [int], @part3 [int], @part4 [int]
    DECLARE @ipBig [bigint]
    DECLARE @countryCode [varchar](2)

    SELECT @part1 = CONVERT([int], SUBSTRING(@ip, 1, dbo.fn_CHARINDEX('.', @ip) - 1))
    SET @ip = SUBSTRING(@ip, dbo.fn_CHARINDEX('.', @ip) + 1, 100)
    SELECT @part2 = CONVERT([int], SUBSTRING(@ip, 1, dbo.fn_CHARINDEX('.', @ip) - 1))
    SET @ip = SUBSTRING(@ip, dbo.fn_CHARINDEX('.', @ip) + 1, 100)
    SELECT @part3 = CONVERT([int], SUBSTRING(@ip, 1, dbo.fn_CHARINDEX('.', @ip) - 1))
    SET @ip = SUBSTRING(@ip, dbo.fn_CHARINDEX('.', @ip) + 1, 100)
    SELECT @part4 = CONVERT([int], @ip)

    SET @ipBig = @part1
    SET @ipBig = @ipBig * 256
    SET @ipBig = @ipBig + @part2
    SET @ipBig = @ipBig * 256
    SET @ipBig = @ipBig + @part3
    SET @ipBig = @ipBig * 256
    SET @ipBig = @ipBig + @part4

    --SELECT TOP 1 @countryCode = [country_code] FROM [dbo].[ip_group_country] WHERE [ip_start] < @ipBig ORDER BY [ip_start] DESC

    RETURN @ipBig
END
GO

ALTER TABLE [dbo].[IISAudit] ADD [IPBig] AS [dbo].[getIpBig]([cIp]) PERSISTED
GO
ALTER TABLE [dbo].[IISAudit] ADD [ValidExt2] AS case substring([csUriStem],charindex('.',[csUriStem]),5) when '.asp' then (1) when '.aspx' then (1) when '.htm' then (1) when '.html' then (1) when '.php' then (1) else (0) end PERSISTED
GO

CREATE INDEX [ix_iisaudit_1]
ON [dbo].[IISAudit](
  [ValidExt2] ASC, [id] ASC
)
INCLUDE ([csUriStem], [IPBig], [DateStr], [TimeStr])
WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE, ONLINE = ON)
ON [PRIMARY];
GO

CREATE CLUSTERED INDEX [ix_ipgroupcountry_1]
ON [dbo].[ip_group_country](
  [ip_start] ASC
)
WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE, ONLINE = ON)
ON [PRIMARY];
GO

Main code:

CREATE TABLE #goalURLs (csUriStem [varchar](255))
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage29.aspx')
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage9.aspx')

EXEC [dbo].[IISLogParse] 900

DROP TABLE #goalURLs

Teardown:

DROP INDEX [ix_iisaudit_1]
ON [dbo].[IISAudit]
GO

-- reorder the rows back to how they were
CREATE CLUSTERED INDEX [ix_iisaudit_1]
ON [dbo].[IISAudit] (id)
GO

DROP INDEX [ix_iisaudit_1]
ON [dbo].[IISAudit]
GO

DROP INDEX [ix_ipgroupcountry_1] ON [dbo].[ip_group_country]
GO

ALTER TABLE [dbo].[IISAudit] DROP COLUMN [IPBig]
GO
ALTER TABLE [dbo].[IISAudit] DROP COLUMN [ValidExt2]
GO
DROP FUNCTION [dbo].[getIpBig]
GO
DROP FUNCTION [dbo].[fn_CHARINDEX]
GO

DROP PROCEDURE [dbo].[IISLogParse]
GO
DROP ASSEMBLY [SqlClassLibrary]
GO
ALTER TABLE [dbo].[IISAudit] DROP COLUMN [id]
GO
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Why is this not an entry? This is by far the quickest thing I have managed to run on the code. Very impressive! :-)
0 Likes 0 ·
Matt is hosting this competition...
0 Likes 0 ·
Yeah, it doesn't feel right entering a competition that I'm hosting. Plus the setup leans heavily on the computed column concept introduced by Peso... I just want to show people that the CLR definitely does have a place in solving problems like this one (and that you can come up with much quicker solutions in terms of the time taken to implement a very fast solution - i.e. no need to do multiple revisions, program it right first time, job done.)
0 Likes 0 ·
Peso avatar image
Peso answered

Ok, this is Peso 2a dated 2010-02-22, and it is quite simplified from earlier version. This simplified code runs in less than 2 seconds on my laptop.

This is the setup

CREATE UNIQUE NONCLUSTERED INDEX UX_IpGroupCountry ON dbo.ip_group_country
(
    ip_start ASC
)
INCLUDE (
        country_code
    )

This is the teardown

DROP INDEX UX_IpGroupCountry ON dbo.ip_group_country

And this is the code

SET NOCOUNT ON

DECLARE @SessionTimeout INT = 900

CREATE TABLE    #Goal
        (
            theURL VARCHAR(255) --COLLATE Finnish_Swedish_CI_AS,
            CONSTRAINT PK_Goal PRIMARY KEY CLUSTERED
            (
                theURL
            )
        )

INSERT  #Goal
    (
        theURL
    )
VALUES  ('ASPNETPage29.aspx'),
    ('ASPNETPage9.aspx')

CREATE TABLE    #Stats
        (
            RowID INT IDENTITY(1, 1),
            CONSTRAINT PK_Stats PRIMARY KEY CLUSTERED
            (
                RowID
            ),      
            cIP VARCHAR(15) NOT NULL,
            DateStr CHAR(10) NOT NULL,
            PageTime DATETIME NOT NULL,
            isGoal TINYINT NOT NULL,
            theURL VARCHAR(255) NOT NULL,
            CountryCode CHAR(2) NOT NULL,
            theVisit INT,
            isBounce TINYINT,
            isEntry TINYINT,
            isExit TINYINT,
            [PageCount] INT,
            theTime INT
        )

INSERT      #Stats
        (
            cIP,
            DateStr,
            PageTime,
            isGoal,
            theURL,
            CountryCode
        )
SELECT      i.cIP,
        i.DateStr,
        CAST(i.DateStr + ' ' + i.TimeStr AS DATETIME) AS PageTime,
        CASE
            WHEN g.theURL IS NULL THEN 0
            ELSE 1
        END AS isGoal,
        i.csUriStem AS theURL,
        igc.CountryCode
FROM        dbo.IISAudit AS i
LEFT JOIN   #Goal AS g ON g.theURL = i.csUriStem
CROSS APPLY (
            SELECT TOP(1)   w.Country_Code
            FROM        dbo.Ip_Group_Country AS w
            WHERE       w.ip_start <= CAST(16777216 AS BIGINT) * PARSENAME(i.cIP, 4) + 65536 * PARSENAME(i.cIP, 3) + 256 * PARSENAME(i.cIP, 2) + PARSENAME(i.cIP, 1)
            ORDER BY    w.ip_start DESC
        ) AS igc(CountryCode)
WHERE       PARSENAME(i.csUriStem, 1) IN ('asp', 'aspx', 'htm', 'html', 'php')
ORDER BY    i.cIP,
        i.Seq_No

DROP TABLE  #Goal

DECLARE @IP VARCHAR(15),
    @PageTime DATETIME,
    @Visit INT = 0

UPDATE  #Stats
SET @Visit = theVisit = CASE
                    WHEN cIP <> @IP THEN @Visit + 1
                    WHEN DATEDIFF(SECOND, @PageTime, PageTime) < @SessionTimeout THEN @Visit
                    ELSE @Visit + 1
                END,
    @IP = cIP,
    @PageTime = PageTime

UPDATE      s
SET     s.isBounce =    CASE 
                    WHEN q.minID = s.RowID THEN q.isBounce
                END,
        s.isEntry = CASE
                    WHEN q.minID = s.RowID THEN 1
                END,
        s.isExit =  CASE
                    WHEN q.maxID = s.RowID THEN 1
                END,
        s.[PageCount] = CASE
                    WHEN q.minID = s.RowID THEN q.[PageCount]
                END,
        s.theTime = CASE
                    WHEN q.minID = s.RowID THEN q.theTime
                END
FROM        #Stats AS s
INNER JOIN  (
            SELECT      w.theVisit,
                    MIN(w.minID) AS minID,
                    MAX(w.maxID) AS maxID,
                    CASE MAX(CASE WHEN s.RowID = w.minID THEN s.theURL ELSE NULL END)
                        WHEN MAX(CASE WHEN s.RowID = w.maxID THEN s.theURL ELSE NULL END) THEN 1
                        ELSE 0
                    END AS isBounce,
                    COUNT(*) AS [PageCount],
                    MAX(w.theTime) AS theTime
            FROM        (
                        SELECT      theVisit,
                                MIN(RowID) AS minID,
                                MAX(RowID) AS maxID,
                                DATEDIFF(SECOND, MIN(PageTime), MAX(PageTime)) AS theTime
                        FROM        #Stats
                        GROUP BY    theVisit
                    ) AS w
            INNER JOIN  #Stats AS s ON s.theVisit = w.theVisit
            GROUP BY    w.theVisit
        ) AS q ON q.theVisit = s.theVisit

-- 1
SELECT      CountryCode,
        CAST(DateStr AS DATETIME) AS DayOnly,
        COUNT(DISTINCT theVisit) AS VisitCount,
        SUM([PageCount]) AS [PageCount],
        MAX([PageCount]) AS MaxPagesPerVisit,
        CAST(1.0E * SUM([PageCount]) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
        MAX(theTime) AS MaxTimeOnSite,
        SUM(theTime) / COUNT(DISTINCT theVisit) AS AvgTimeOnSite,
        SUM(isGoal) AS GoalCount,
        CAST(100.0E * SUM(CASE WHEN [PageCount] = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Stats
GROUP BY    CountryCode,
        DateStr
ORDER BY    DateStr,
        CountryCode

-- 2
SELECT      CountryCode,
        COUNT(DISTINCT theVisit) AS VisitCount,
        SUM([PageCount]) AS [PageCount],
        MAX([PageCount]) AS MaxPagesPerVisit,
        CAST(1.0E * SUM([PageCount]) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
        MAX(theTime) AS MaxTimeOnSite,
        SUM(theTime) / COUNT(DISTINCT theVisit) AS AvgTimeOnSite,
        SUM(isGoal) AS GoalCount,
        CAST(100.0E * SUM(CASE WHEN [PageCount] = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Stats
GROUP BY    CountryCode
ORDER BY    CountryCode

-- 3
SELECT      theURL AS uriStem,
        CAST(DateStr AS DATETIME) AS PageDay,
        COUNT(*) AS [PageCount],
        CAST(100.0E * COALESCE(SUM(isEntry), 0) / COUNT(*) AS DECIMAL(12, 2)) AS EntryRate,
        CAST(100.0E * COALESCE(SUM(isExit), 0) / COUNT(*) AS DECIMAL(12, 2)) AS ExitRate,
        CAST(100.0E * COALESCE(SUM(isBounce), 0) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Stats
GROUP BY    theURL,
        DateStr
ORDER BY    DateStr,
        theURL

DROP TABLE  #Stats
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I get this error when I try to run it: "Msg 468, Level 16, State 9, Line 42 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Finnish_Swedish_CI_AS" in the equal to operation. Msg 207, Level 16, State 1, Line 42 Invalid column name 'Seq_No'."
0 Likes 0 ·
Remove the COLLATE thing. I have to use it on my laptop due to my current setup.
0 Likes 0 ·
I am also using the new set of sample data with an explicit sequence numbering.
0 Likes 0 ·
This is the most accurate yet! It is only inaccurate for 4 records of 696 in resultset 1, 2 of 131 in resultset 2 and 4 of 1332 in resultset 3.
0 Likes 0 ·
Hmm tried that and still got errors. I will try again tonight.
0 Likes 0 ·
Show more comments
dave ballantyne avatar image
dave ballantyne answered

Version 1a Performance seem ok , doesnt quite match with reggies output data.

The setup

CREATE TABLE #goalURLs (csUriStem [varchar](255))
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage29.aspx')
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage9.aspx')
go
Create unique index idxCtryIp on ip_group_country (Ip_start) include (Country_name)
go
Create index idxIpVisitTime on IISAudit (Cip,DateStr,TimeStr)
go
Drop Function CountForUnique
go
Create Function CountForUnique(@Ip char(50),
                               @Date as char(10),
                               @Time as char(8),
                               @SinceXHours as integer)

returns table
as
return(
with cteDateTime
as
(
select dateadd(hh,0-@SinceXHours,convert(datetime,@Date +' '+@Time+'.000' )) as SinceDTime
),
cteSplit
as
(
Select CONVERT(char(10), SinceDTime,120) as DateFrom,
       CONVERT(char(10), SinceDTime,108) as TimeFrom
 from  cteDateTime
),
ctePreRet
as
(
select COUNT(*) as Count
 from  cteSplit ,IISAudit IIS
where  (IIS.DateStr > DateFrom or (IIS.DateStr=DateFrom and IIS.TimeStr >= TimeFrom))
 and   (IIS.DateStr < @Date    or (IIS.DateStr=@Date    and IIS.TimeStr <  @Time))
 and   IIS.cIp = @Ip
)
Select Case when Ctepreret.Count =0 then 1 else 0 end as CntUnique

from ctePreRet
)

go


Drop function SplitIp
go

create function SplitIp(@IpAddress char(20))
returns table
as
return(
with cteNum
as
(
Select 1 as n union all select 2 union all
Select 3 union all select 4 union all
Select 5 as n union all select 6 union all
Select 7 union all select 8 union all
Select 9 as n union all select 10 union all

Select 11 as n union all select 12 union all
Select 13 union all select 14 union all
Select 15 as n union all select 16 union all
Select 17 union all select 18 union all
Select 19 as n union all select 20 
),
ctePosn
as
(
select 1 as posn union 
Select len(@Ipaddress)+1 union
Select cteNum.n
  from cteNum
  where SUBSTRING(@Ipaddress,cteNum.n,1)='.'
),
cteCutPosn
as
(
Select min([1]) as Pos1,min([2]) as Pos2,min([3]) as Pos3,min([4]) as Pos4,min([5]) as Pos5
 from  (
    Select Posn,Row_Number() over(order by posn) as Rown from ctePosn ) as inp
    pivot(
        min(posn)
        for Rown in ([1],[2],[3],[4],[5])
    ) as pvt
)
Select cast(SUBSTRING(@Ipaddress,Pos1,Pos2-Pos1)     as bigint) * 16777216 +
       cast(SUBSTRING(@Ipaddress,Pos2+1,Pos3-1-Pos2) as integer) *  65536 + 
       cast(SUBSTRING(@Ipaddress,Pos3+1,Pos4-1-Pos3) as integer) * 256 +
       cast(SUBSTRING(@Ipaddress,Pos4+1,Pos5-1-Pos4) as integer) as IpNum
  from cteCutPosn
)
go

drop Function isValidExt
go
create Function isValidExt(@UriStem varchar(255))
returns table
as
return
(

with ctePos

as
(
   Select right(@UriStem,5) as rght
)
,
cteExt
as
(
Select case when left(rght,1) = '.' then
          substring(rght,2,5) else
          rght end as ext
 from  ctePos
)
Select 1 as ValidExt
  from cteExt
  join (Select 'asp' as tExt union all
        Select 'aspx' union all
        Select 'htm' union all
        select 'html' union all
        select 'php') as tExt on cteExt.ext = tExt.tExt
)

go

And the execution code

Drop TAble #VisitLog
go
Drop Table #VisitSummary
go
Create TAble #VisitLog
(
IpAddress char(20),
DateStr  char(10),
TimeStr  Char(10),
DateTime dateTime,
IpVisit   integer,
GoalCount integer,
Uri       varchar(255),
LogId     integer 
)
go
Create unique clustered index visitlogidx on #visitlog(IpAddress,DateTime,LogId)
go
insert into #VisitLog(IpAddress,DateStr,TimeStr,DateTime,ipVisit,GoalCount,Uri,LogId)
select IIS.cIp,DateStr,Timestr,
       convert(datetime,DateStr +' '+TimeStr+'.000' ) as SinceDTime,0,
       sum(case when #goalURLs.csUriStem is not null then  1 else 0 end),
       IIS.csUriStem,
       ROW_NUMBER() over (partition by IIS.cIp order by DateStr,Timestr)
from IISAudit IIS
cross apply dbo.isValidExt(csUriStem)
left join #goalURLs
    on #goalURLs.csUriStem = IIS.csUriStem

group by cIp,DateStr,Timestr,convert(datetime,DateStr +' '+TimeStr+'.000' ) ,IIS.csUriStem
go
Declare @SessionTimeOut integer
Declare @VisitCount integer
Declare @SessionTimesOutWhen datetime
Declare @IpOn                varchar(20)
Declare @VisitView           integer
Select @IpOn = '0.0'
select @VisitCount =0
Declare @LVisit integer
Select @SessionTimeOut = 900

update #visitlog set ipvisit =0
update #VisitLog
   set @VisitCount = case when @IpOn <> #VisitLog.IpAddress then 0 
                          when #VisitLog.DateTime  > @SessionTimesOutWhen then @VisitCount+1
                          else @VisitCount  end,
       @SessionTimesOutWhen = case when @VisitCount =0  or #VisitLog.DateTime  > @SessionTimesOutWhen
                              then DATEADD(s,@SessionTimeOut,#VisitLog.DateTime) else @SessionTimesOutWhen end,
       @VisitCount = case when @VisitCount =0 then 1 else @VisitCount end,

       IpVisit = @VisitCount,
       @IpOn=  #VisitLog.IpAddress
 from  #VisitLog
 OPTION (MAXDOP 1)
go

Create index uri on #visitlog(uri,DateTime)
go
Create Table #VisitSummary
(
IpAddress char(20) not null,
SessionStart datetime not null,
SessionEnd Datetime not null,
VisitCount integer not null,
Pages      integer not null,
GoalCount  integer not null,
SessionEntryPage varchar(255) not null,
SessionExitPage varchar(255) not null
)
go
Create unique Clustered index idxVisitSummary on #VisitSummary(IpAddress,SessionStart)
go

with ctePageSum
as
(
 select ipAddress,DateTime,IpVisit,GoalCount,
        Uri,
        LogId,
        ROW_NUMBER() over (partition by ipAddress,IpVisit order by LogId) as StartRowN,
        ROW_NUMBER() over (partition by ipAddress,IpVisit order by LogId desc) as EndRowN
  from #VisitLog
)
insert into #VisitSummary(IpAddress,SessionStart,SessionEnd,VisitCount,Pages,GoalCount,SessionEntryPage,SessionExitPage)
Select ipAddress,
       min(case when StartRowN =1 then DateTime end),
       min(case when EndRowN =1 then DateTime end),
       IpVisit,
       count(*),
       SUM(GoalCount),
       min(case when StartRowN =1 then Uri end),
       min(case when EndRowN =1 then Uri end)
  from ctePageSum        
 group by ipAddress,IpVisit
go
with cteResults
as
(
select Ctry.country_code , 
       count(*)   as VisitCount,
       sum(#VisitSummary.Pages) as PagesCount,
       max(#VisitSummary.Pages) as MaxPagesPerCount,
       Avg(#VisitSummary.Pages) as AvgPagesPerCount,
       max(datediff(minute,SessionStart,SessionEnd)) as MaxTimeOnSite,
       avg(datediff(minute,SessionStart,SessionEnd)) as AvgTimeOnSite,
       SUM(#VisitSummary.GoalCount) as GoalCount,
       sum(case when #VisitSummary.Pages = 1 then 1 else 0 end) BounceCount
  from #VisitSummary
 cross apply dbo.SplitIp(#VisitSummary.IpAddress) nIp
 cross apply ( Select top(1) country_code
                 from ip_group_country 
                where ip_start  < = nIp.IpNum
                order by ip_start desc)  as Ctry

group by Ctry.country_code
)
Select Country_Code,visitcount,pagescount,maxPagesPercount,AvgPagesPerCount,
       MaxTimeOnSite,AvgTimeOnSite,GoalCount,
       case when BounceCount =0 then 0 else 100.0/(VisitCount/cast(BounceCount as decimal(15,5))) end as BounceRate
 from  cteResults
order by 1
go
with cteResults
as
(
select Ctry.country_code , 
       cast (DATEDIFF(DAY,0,SessionStart) AS datetime) as Day,
       count(*)   as VisitCount,
       sum(#VisitSummary.Pages) as PagesCount,
       max(#VisitSummary.Pages) as MaxPagesPerCount,
       Avg(#VisitSummary.Pages) as AvgPagesPerCount,
       max(datediff(minute,SessionStart,SessionEnd)) as MaxTimeOnSite,
       avg(datediff(minute,SessionStart,SessionEnd)) as AvgTimeOnSite,
       SUM(#VisitSummary.GoalCount) as GoalCount,
       sum(case when #VisitSummary.Pages = 1 then 1 else 0 end) BounceCount
  from #VisitSummary
 cross apply dbo.SplitIp(#VisitSummary.IpAddress) nIp
 cross apply ( Select top(1) country_code
                 from ip_group_country 
                where ip_start  < = nIp.IpNum
                order by ip_start desc)  as Ctry

group by Ctry.country_code,DATEDIFF(DAY,0,SessionStart)
)
Select Country_Code,day,visitcount,pagescount,maxPagesPercount,AvgPagesPerCount,
       MaxTimeOnSite,AvgTimeOnSite,GoalCount,
       case when BounceCount =0 then 0 else 100.0/(VisitCount/cast(BounceCount as decimal(15,5))) end as BounceRate
 from  cteResults
order by 1,2
go

with cteEntry
as
(
select DATEDIFF(DAY,0,SessionStart) as SessDate,
       SessionEntryPage,
       count(*) as EntryCount,
       SUM(case when SessionExitPage = SessionEntryPage then 1 else 0 end) as BounceCount
  from #VisitSummary
 group by DATEDIFF(DAY,0,SessionStart),
       SessionEntryPage
),
cteExit
as
(
select DATEDIFF(DAY,0,SessionStart) as SessDate,
       SessionExitPage,
       count(*) as ExitCount
  from #VisitSummary
 group by DATEDIFF(DAY,0,SessionStart),
       SessionExitPage
),
cteVisit
as
( 
select DATEDIFF(DAY,0,DateTime) as VisitDate,
       uri,
       count(*) as VisitCount
  from #VisitLog
 group by DATEDIFF(DAY,0,DateTime),Uri
),
ctePageSumm
as
(
Select VisitDate,
       uri,
       cast(isNull(EntryCount,0) as decimal(10,5)) as EntryCount,
       cast(isNull(ExitCount,0)  as decimal(10,5)) as Exitcount,
       cast(isNull(VisitCount,0) as decimal(10,5)) as VisitCount,
       cast(ISNULL(BounceCount,0) as decimal(10,5)) as BounceCount
  from cteVisit 
  left join cteEntry
    on cteEntry.SessDate  = cteVisit.VisitDate
   and cteEntry.SessionEntryPage = cteVisit.uri
  left join cteExit
    on cteExit.SessDate  = cteVisit.VisitDate
   and cteExit.SessionExitPage = cteVisit.uri
)
Select CAST(VisitDate AS datetime),
       uri,
       VisitCount AS PageCount,
       EntryCount,
       case when EntryCount =0 then 0 else 100.0 / (VisitCount / EntryCount  ) end  as EntryRate,
       case when ExitCount =0 then 0 else 100.0 / (VisitCount / ExitCount  ) end    as ExitRate,
       case when BounceCount =0 then 0 else 100.0 / (VisitCount / BounceCount  ) end as BounceRate
 from ctePageSumm
order by 1,2
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave - i had to mess about with it a bit to get it into the setup / teardown format that the harness wants, but good effort. You still have the harness for doing data comparisons, right?
0 Likes 0 ·
dave ballantyne avatar image
dave ballantyne answered

Dave Ver 1.b - I think the results are now spot on ,bold claim i know

The Setup

Create unique clustered index idxCtryIp on ip_group_country (Ip_start) 
go
Create index idxIpVisitTime on IISAudit (Cip,DateStr,TimeStr)
go
create function SplitIp(@IpAddress char(20))
returns table
as
return(
with cteposn1
as(
select CHARINDEX('.',@ipaddress) as pos1
),
cteposn2
as
(
select pos1,CHARINDEX('.',@ipaddress,pos1+1) as pos2
 from cteposn1
),
cteposn3
as
(
select pos1,pos2,CHARINDEX('.',@ipaddress,pos2+1) as pos3
 from cteposn2
),
cteposn4
as
(
select pos1,pos2,pos3
  from cteposn3
)

select cast(LEFT(@ipaddress,pos1-1) as bigint) * 16777216 +
       cast(SUBSTRING(@ipaddress,pos1+1,(pos2-pos1)-1) as integer) *  65536 +
       cast(SUBSTRING(@ipaddress,pos2+1,(pos3-pos2)-1) as integer) * 256 +
       cast(SUBSTRING(@ipaddress,pos3+1,(LEN(@ipaddress)-pos3))  as integer) as IpNum   
  from cteposn4
)

go
create Function isValidExt(@UriStem varchar(255))
returns table
as
return
(

with ctePos

as
(
   Select right(@UriStem,4) as rght
)
,
cteExt
as
(
Select case when left(rght,1) = '.' then
          substring(rght,2,4) else
          rght end as ext
 from ctePos
)
Select 1 as ValidExt
  from cteExt
  join (Select 'asp' as tExt union all
        Select 'aspx' union all
        Select 'htm' union all
        select 'html' union all
        select 'php') as tExt on cteExt.ext = tExt.tExt
)

go

The Teardown

drop index ip_group_country.idxCtryIp
drop index idxIpVisitTime.IISAudit
Drop function SplitIp
drop Function isValidExt

And the execution code

set nocount on 

CREATE TABLE #goalURLs (csUriStem [varchar](255))
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage29.aspx')
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage9.aspx')




Create TAble #VisitLog
(
IpAddress char(20),
DateStr  char(10),
TimeStr  Char(10),
DateTime dateTime,
IpVisit   integer,
GoalCount integer,
Uri       varchar(255),
LogId     integer 
)
Create unique clustered index visitlogidx on #visitlog(IpAddress,DateTime,LogId)
insert into #VisitLog(IpAddress,DateStr,TimeStr,DateTime,ipVisit,GoalCount,Uri,LogId)
select IIS.cIp,DateStr,Timestr,
       convert(datetime,DateStr +' '+TimeStr+'.000' ) as SinceDTime,0,
       case when #goalURLs.csUriStem is not null then  1 else 0 end,
       IIS.csUriStem,
       ROW_NUMBER() over (partition by IIS.cIp order by DateStr,Timestr)
from IISAudit IIS
cross apply dbo.isValidExt(csUriStem)
left join #goalURLs
    on #goalURLs.csUriStem = IIS.csUriStem

Declare @SessionTimeOut integer
Declare @VisitCount integer
Declare @SessionTimesOutWhen datetime
Declare @IpOn                varchar(20)
Declare @VisitView           integer
Select @IpOn = '0.0'
select @VisitCount =0
Declare @LVisit integer
Select @SessionTimeOut = 900


update #VisitLog
   set @VisitCount = case when @IpOn <> #VisitLog.IpAddress then 0 
                          when #VisitLog.DateTime  > @SessionTimesOutWhen then @VisitCount+1
                          else @VisitCount  end,
       @SessionTimesOutWhen =  DATEADD(s,@SessionTimeOut,#VisitLog.DateTime),
       @VisitCount = case when @VisitCount =0 then 1 else @VisitCount end,

       IpVisit = @VisitCount,
       @IpOn=  #VisitLog.IpAddress
 from  #VisitLog
 OPTION (MAXDOP 1)

Create index uri on #visitlog(uri,DateTime)

Create Table #VisitSummary
(
IpAddress char(20) not null,
CountryCode char(2) not null,
SessionStart datetime not null,
SessionEnd Datetime not null,
VisitCount integer not null,
Pages      decimal(6,2) not null,
GoalCount  integer not null,
SessionEntryPage varchar(255) not null,
SessionExitPage varchar(255) not null
)
Create unique Clustered index idxVisitSummary on #VisitSummary(IpAddress,SessionStart)

;with ctePageSum
as
(
 select ipAddress,DateTime,IpVisit,GoalCount,
        Uri,
        LogId,
        ROW_NUMBER() over (partition by ipAddress,IpVisit order by LogId) as StartRowN,
        ROW_NUMBER() over (partition by ipAddress,IpVisit order by LogId desc) as EndRowN
  from #VisitLog
)
insert into #VisitSummary(IpAddress,SessionStart,SessionEnd,VisitCount,Pages,GoalCount,SessionEntryPage,SessionExitPage,CountryCode)
Select ipAddress,
       min(case when StartRowN =1 then DateTime end),
       min(case when EndRowN =1 then DateTime end),
       IpVisit,
       count(*),
       SUM(GoalCount),
       min(case when StartRowN =1 then Uri end),
       min(case when EndRowN =1 then Uri end),
       ctry.country_code

  from ctePageSum   
  cross apply dbo.SplitIp(ctePageSum.IpAddress) nIp
  cross apply ( Select top(1) country_code
                 from ip_group_country 
                where ip_start  < = nIp.IpNum
                order by ip_start desc)  as Ctry

 group by ipAddress,IpVisit, nIp.IpNum,Ctry.country_code;



;with cteResults
as
(
select countrycode , 
       cast (DATEDIFF(DAY,0,SessionStart) AS datetime) as DayOnly,
       count(*)   as VisitCount,
       sum(#VisitSummary.Pages) as PageCount,
       max(#VisitSummary.Pages) as MaxPagesPerVisit,
       round(Avg(#VisitSummary.Pages),2) as AvgPagesPerVisit,
       max(datediff(SECOND,SessionStart,SessionEnd)) as MaxTimeOnSite,
       round(avg(datediff(SECOND,SessionStart,SessionEnd)),2) as AvgTimeOnSite,
       SUM(#VisitSummary.GoalCount) as GoalCount,
       sum(case when #VisitSummary.Pages = 1 then 1 else 0 end) BounceCount
  from #VisitSummary
group by countrycode,DATEDIFF(DAY,0,SessionStart)
)
Select CountryCode,dayOnly,visitcount,pagecount,maxPagesPerVisit,AvgPagesPerVisit,
       MaxTimeOnSite,AvgTimeOnSite,GoalCount,
       round(case when BounceCount =0 then 0 else 100.0/(VisitCount/cast(BounceCount as decimal(15,5))) end ,2) as BounceRate
 from  cteResults
order by 2,1

;with cteResults
as
(
select countrycode , 
       count(*)   as VisitCount,
       sum(#VisitSummary.Pages) as PageCount,
       max(#VisitSummary.Pages) as MaxPagesPerVisit,
       round(Avg(#VisitSummary.Pages),2) as AvgPagesPerCount,
       max(datediff(second,SessionStart,SessionEnd)) as MaxTimeOnSite,
       avg(datediff(second,SessionStart,SessionEnd)) as AvgTimeOnSite,
       SUM(#VisitSummary.GoalCount) as GoalCount,
       sum(case when #VisitSummary.Pages = 1 then 1 else 0 end) BounceCount
  from #VisitSummary
group by countrycode
)
Select CountryCode,visitcount,pagecount,maxPagesPerVisit,AvgPagesPerCount,
       MaxTimeOnSite,AvgTimeOnSite,GoalCount,
       round(case when BounceCount =0 then 0 else 100.0/(VisitCount/cast(BounceCount as decimal(15,5)))end ,2) as BounceRate

 from  cteResults
order by 1


;with cteEntry
as
(
select DATEDIFF(DAY,0,SessionStart) as SessDate,
       SessionEntryPage,
       count(*) as EntryCount,
       SUM(case when SessionExitPage = SessionEntryPage then 1 else 0 end) as BounceCount
  from #VisitSummary
 group by DATEDIFF(DAY,0,SessionStart),
       SessionEntryPage
),
cteExit
as
(
select DATEDIFF(DAY,0,SessionStart) as SessDate,
       SessionExitPage,
       count(*) as ExitCount
  from #VisitSummary
 group by DATEDIFF(DAY,0,SessionStart),
       SessionExitPage
),
cteVisit
as
( 
select DATEDIFF(DAY,0,DateTime) as VisitDate,
       uri,
       count(*) as VisitCount
  from #VisitLog
 group by DATEDIFF(DAY,0,DateTime),Uri
),
ctePageSumm
as
(
Select VisitDate,
       uri,
       cast(isNull(EntryCount,0) as decimal(10,5)) as EntryCount,
       cast(isNull(ExitCount,0)  as decimal(10,5)) as Exitcount,
       cast(isNull(VisitCount,0) as decimal(10,5)) as VisitCount,
       cast(ISNULL(BounceCount,0) as decimal(10,5)) as BounceCount
  from cteVisit 
  left join cteEntry
    on cteEntry.SessDate  = cteVisit.VisitDate
   and cteEntry.SessionEntryPage = cteVisit.uri
  left join cteExit
    on cteExit.SessDate  = cteVisit.VisitDate
   and cteExit.SessionExitPage = cteVisit.uri
)
Select uri   as UriStem,
       CAST(VisitDate AS datetime) as PageDay,
       VisitCount AS PageCount,
       EntryCount,
       round(case when EntryCount =0 then 0 else 100.0 / (VisitCount / EntryCount  ) end,2)  as EntryRate,
       round(case when ExitCount =0 then 0 else 100.0 / (VisitCount / ExitCount  ) end ,2)   as ExitRate,
       round(case when BounceCount =0 then 0 else 100.0 / (VisitCount / BounceCount  ) end,2) as BounceRate
 from ctePageSumm
order by 1,2

Drop TAble #goalURLs
Drop TAble #VisitLog
Drop Table #VisitSummary
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso avatar image
Peso answered

Peso 3a, almost there. In this solution there are no "quirkyness" at all. Resultset 1 and 2 are perfect match, but resultset #3 still need some work.

SET NOCOUNT ON

CREATE TABLE    #Goal
        (
            theURL VARCHAR(255) COLLATE FINNISH_SWEDISH_CI_AS,
            CONSTRAINT PK_Goal PRIMARY KEY CLUSTERED
            (
                theURL
            )
        )

INSERT  #Goal
    (
        theURL
    )
VALUES  ('ASPNETPage29.aspx'),
    ('ASPNETPage9.aspx')

DECLARE @SessionTimeout INT = 900

CREATE TABLE    #Source
        (
            cIP VARCHAR(15) NOT NULL,
            PageTime DATETIME NOT NULL,
            recID INT NOT NULL,
            CONSTRAINT PK_Source PRIMARY KEY CLUSTERED
            (
                cIP,
                PageTime,
                recID
            ),
            csUriStem VARCHAR(255) NOT NULL,
            IsGoal INT NOT NULL,
            RowID INT
        )

INSERT      #Source
        (
            cIP,
            PageTime,
            recID,
            csUriStem,
            IsGoal
        )
SELECT      i.cIP,
        i.DateStr + ' ' + i.TimeStr AS PageTime,
        ROW_NUMBER() OVER (PARTITION BY i.cIP ORDER BY i.DateStr, i.TimeStr) AS recID,
        i.csUriStem,
        CASE
            WHEN g.theURL IS NULL THEN 0
            ELSE 1
        END AS IsGoal
FROM        dbo.IISAudit AS i
LEFT JOIN   #Goal AS g ON g.theURL = i.csUriStem
WHERE       PARSENAME(i.csUriStem, 1) IN ('asp', 'aspx', 'htm', 'html', 'php')
        --AND cIP = '82.75.9.215'

DROP TABLE  #Goal

CREATE TABLE    #Lower
        (
            cIP VARCHAR(15) NOT NULL,
            StartTime DATETIME NOT NULL,
            recID INT NOT NULL,
            CONSTRAINT PK_Lower PRIMARY KEY CLUSTERED
            (
                cIP,
                StartTime,
                recID
            ),
            csUriStem VARCHAR(255) NOT NULL
        )

INSERT      #Lower
        (
            cIP,
            StartTime,
            recID,
            csUriStem
        )
SELECT      s.cIP,
        s.PageTime AS StartTime,
        ROW_NUMBER() OVER (PARTITION BY s.cIP ORDER BY s.PageTime) AS recID,
        s.csUriStem
FROM        #Source AS s
LEFT HASH JOIN  #Source AS t ON t.cIP = s.cIP
            AND t.recID = s.recID - 1
WHERE       DATEADD(SECOND, @SessionTimeout, t.PageTime) < s.PageTime
        OR t.cIP IS NULL

CREATE TABLE    #Upper
        (
            cIP VARCHAR(15) NOT NULL,
            EndTime DATETIME NOT NULL,
            recID INT NOT NULL,
            CONSTRAINT PK_Upper PRIMARY KEY CLUSTERED
            (
                cIP,
                EndTime,
                recID
            ),
            csUriStem VARCHAR(255) NOT NULL
        )

INSERT      #Upper
        (
            cIP,
            EndTime,
            recID,
            csUriStem
        )
SELECT      s.cIP,
        DATEADD(SECOND, @SessionTimeout, s.PageTime) AS EndTime,
        ROW_NUMBER() OVER (PARTITION BY s.cIP ORDER BY s.PageTime) AS recID,
        s.csUriStem
FROM        #Source AS s
LEFT HASH JOIN  #Source AS t ON t.cIP = s.cIP
            AND t.recID = s.recID + 1
WHERE       DATEADD(SECOND, @SessionTimeout, s.PageTime) < t.PageTime
        OR t.cIP IS NULL

CREATE TABLE    #Result
        (
            RowID INT IDENTITY(1, 1) NOT NULL,
            cIP VARCHAR(15) NOT NULL,
            StartTime DATETIME NOT NULL,
            EndTime DATETIME NOT NULL,
            CONSTRAINT PK_Result PRIMARY KEY CLUSTERED
            (
                cIP,
                StartTime,
                EndTime
            ),
            DayOnly DATETIME NOT NULL,
            TimeOnSite INT NOT NULL,
            firstURL VARCHAR(255) NOT NULL,
            lastURL VARCHAR(255) NOT NULL,
            IsGoal INT,
            [PageCount] INT,
            CountryCode CHAR(2)
        )

INSERT      #Result
        (
            cIP,
            StartTime,
            EndTime,
            DayOnly,
            TimeOnSite,
            firstURL,
            lastURL
        )
SELECT      l.cIP,
        l.StartTime,
        u.EndTime,
        DATEDIFF(DAY, 0, l.StartTime) AS DayOnly,
        DATEDIFF(SECOND, l.StartTime, u.EndTime) - 900 AS TimeOnSite,
        l.csUriStem AS firstURL,
        u.csUriStem AS lastURL
FROM        #Lower AS l
INNER HASH JOIN #Upper AS u ON u.cIP = l.cIP
WHERE       l.recID = u.recID
ORDER BY    l.cIP,
        l.recID

DROP TABLE  #Lower,
        #Upper

UPDATE      s
SET     s.RowID = r.RowID
FROM        #Result AS r
INNER JOIN  #Source AS s ON s.PageTime BETWEEN r.StartTime AND r.EndTime
WHERE       s.cIP = r.cIP

UPDATE      r
SET     r.IsGoal = w.IsGoal,
        r.[PageCount] = w.[PageCount]
FROM        #Result AS r
INNER JOIN  (
            SELECT      RowID,
                    SUM(IsGoal) AS IsGoal,
                    COUNT(*) AS [PageCount]
            FROM        #Source
            GROUP BY    RowID
        ) AS w ON w.RowID = r.RowID

CREATE TABLE    #Lookup
        (
            cIP VARCHAR(15) NOT NULL,
            IP BIGINT,
            CountryCode VARCHAR(2)
        )

INSERT      #Lookup
        (
            cIP
        )
SELECT      cIP
FROM        #Result
GROUP BY    cIP

UPDATE  #Lookup
SET IP = CAST(16777216 AS BIGINT) * PARSENAME(cIP, 4) + 65536 * PARSENAME(cIP, 3) + 256 * PARSENAME(cIP, 2) + PARSENAME(cIP, 1)

UPDATE  l
SET l.CountryCode = (SELECT TOP(1) x.Country_Code FROM ip_group_country AS x WHERE x.ip_start <= l.IP ORDER BY x.ip_start DESC)
FROM    #Lookup AS l

UPDATE      r
SET     r.CountryCode = l.CountryCode
FROM        #Result AS r
INNER JOIN  #Lookup AS l ON l.cIP = r.cIP

DROP TABLE  #Lookup

-- 1
SELECT      CountryCode,
        DayOnly,
        COUNT(*) AS VisitCount,
        SUM([PageCount]) AS [PageCount],
        MAX([PageCount]) AS MaxPagesPerVisit,
        CAST(1.0E * SUM([PageCount]) / COUNT(*) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
        MAX(TimeOnSite) AS MaxTimeOnSite,
        SUM(TimeOnSite) / COUNT(*) AS AvgTimeOnSite,
        SUM(isGoal) AS GoalCount,
        CAST(100.0E * SUM(CASE WHEN [PageCount] = 1 THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Result
GROUP BY    CountryCode,
        DayOnly
ORDER BY    DayOnly,
        CountryCode

-- 2
SELECT      CountryCode,
        COUNT(*) AS VisitCount,
        SUM([PageCount]) AS [PageCount],
        MAX([PageCount]) AS MaxPagesPerVisit,
        CAST(1.0E * SUM([PageCount]) / COUNT(*) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
        MAX(TimeOnSite) AS MaxTimeOnSite,
        SUM(TimeOnSite) / COUNT(*) AS AvgTimeOnSite,
        SUM(isGoal) AS GoalCount,
        CAST(100.0E * SUM(CASE WHEN [PageCount] = 1 THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Result
GROUP BY    CountryCode
ORDER BY    CountryCode

-- 3
SELECT      uriStem,
        PageDay,
        SUM([PageCount]) AS [PageCount],
        CAST(100.0E * SUM(IsEntry) / SUM([PageCount]) AS DECIMAL(12, 2)) AS EntryRate,
        CAST(100.0E * SUM(IsExit) / SUM([PageCount]) AS DECIMAL(12, 2)) AS ExitRate,
        CAST(100.0E * SUM(IsBounce) / SUM([PageCount]) AS DECIMAL(12, 2)) AS BounceRate
FROM        (
            SELECT  firstURL AS uriStem,
                DayOnly AS PageDay,
                1 AS IsEntry,
                0 AS IsExit,
                CASE firstURL
                    WHEN lastURL THEN 1
                    ELSE 0
                END AS IsBounce,
                0 AS [PageCount]
            FROM    #Result

            UNION ALL

            SELECT  lastURL AS uriStem,
                DayOnly AS PageDay,
                0 AS IsEntry,
                1 AS IsExit,
                0 AS IsBounce,
                0 AS [PageCount]
            FROM    #Result

            UNION ALL

            SELECT  csUriStem AS uriStem,
                DATEDIFF(DAY, 0, PageTime) AS PageDay,
                0 AS IsEntry,
                0 AS IsExit,
                0 AS IsBounce,
                1 AS [PageCount]
            FROM    #Source
        ) AS d
GROUP BY    PageDay,
        uriStem
ORDER BY    PageDay,
        uriStem

DROP TABLE  #Result,
        #Source
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you want me to add this to the harness or wait until the match is there? I'll do another timing run tomorrow (just off to bed)
0 Likes 0 ·
Add it, so I can see if I am going the right direction.
0 Likes 0 ·
It's now up there...
0 Likes 0 ·
And it's almost valid, or just plain invalid? ;-)
0 Likes 0 ·
I'm not sure actually - I was so tired yesterday I just ran it quickly and went to sleep! But it looks like 2c is doing to clinch it... unless someone submits an amazingly fast and correct solution in the next 4 minutes
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.