Solved

# charts of popularity (like music charts)

Posted on 2003-02-25
Medium Priority
367 Views
hi

given a table which logs everytime someone access an article

articlelog

id        integer (primary key)
articleid integer
userid    text(20)
date      shortdate

ie

1 amoran 34 25/02/2003
2 amoran 65 24/02/2003
3 amoran 2 24/02/2003
4 amoran 645 24/02/2003
5 amoran 554 23/02/2003
6 amoran 364 23/02/2003
7 amoran 344 23/02/2003

is there a way to generate a chart which says this weeks most 10 most popular articles (and if possible) what possition that article was at last week

so the current week would be the last seven days

thanks
0
Question by:amoran
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 6
• 6
• +2

Expert Comment

ID: 8015720
Yeah just group by article ID with count and order by count. Limit by current week or previous week for week by week analysis.
0

Author Comment

ID: 8015835
but id like to do something like

this week - last week - articleid
1 - 3 - 24
2 - 2 - 12
3 - 1 - 29
4 - / - 30

(where / means it wasnt in last weeks top ten)

etc
0

LVL 23

Accepted Solution

ID: 8015969
Hi.
Try this:

CREATE TABLE #ptblLastWeek
(
position INTEGER IDENTITY(1,1),
articleid INTEGER
)

CREATE TABLE #ptblThisWeek
(
position INTEGER IDENTITY(1,1),
articleid INTEGER
)

'-- Get last week's TOP 10
INSERT #ptblLastWeek (articleid)
SELECT TOP 10 articleid
FROM articlelog
WHERE DATEDIFF(wk, [date], GETDATE()) = 1
GROUP BY articleid
ORDER BY COUNT(articleid) DESC, articleid ASC

'-- Get this week's TOP 10
INSERT #ptblThisWeek (articleid)
SELECT TOP 10 articleid
FROM articlelog
WHERE DATEDIFF(wk, [date], GETDATE()) = 0
GROUP BY articleid
ORDER BY COUNT(articleid) DESC, articleid ASC

SELECT tw.position "ThisWeek",
ISNULL((SELECT CAST(lw.position AS VARCHAR)FROM #ptblLastWeek lw WHERE lw.articleid = tw.articleid), '/') "LastWeek",
tw.articleid
FROM #ptblThisWeek tw

DROP TABLE #ptblLastWeek
DROP TABLE #ptblThisWeek
0

Author Comment

ID: 8016660
i got a syntax error i n create table statement
0

LVL 23

Expert Comment

ID: 8016754
sorry, the problem isnt in the CREATE TABLE statement, it's where I've put the comments.

Find these 2 comments in my query:
'-- Get last week's TOP 10
'-- Get this week's TOP 10

and remove the single quote at the beginning of each line.
i.e.:
-- Get last week's TOP 10
-- Get this week's TOP 10

0

LVL 69

Assisted Solution

Scott Pletcher earned 164 total points
ID: 8016798
I suggest making it a permanent table.  You don't want the chart somehow changing between weeks.  You could either keep separate rows for current and last or one row with columns for current and prior week (denormalized, but should be OK here as it shouldn't change).
0

LVL 23

Expert Comment

ID: 8018021
amoran, did you get the query to work?
0

Assisted Solution

Panimu earned 164 total points
ID: 8018931
Making a permanent table is bad management as the data can always be constructed from other data.
0

LVL 69

Expert Comment

ID: 8019009
There are exceptions to every rule.  Knowing when it is worthwhile to break the rules is the key to being a good DBA, programmer, etc..  In my view, in this situation it is worth it, because the list from the prior week MUST remain EXACTLY as it was reported then.  Futhermore, this info will likely be accessed frequently enough that it isn't worth the overhead to continually re-compute it.
0

Author Comment

ID: 8024362

isnt this table created and dropped everytime some accesses this webpage?

is there a way to do it without creating and dropping tables?

thanks again
0

LVL 23

Expert Comment

ID: 8024559
Yes, the table is created and dropped each time the procedure is called - temporary tables allow temporary storage of results leading up to a final recordset.

Scott's suggestion is a very valid point and I'd recommend you incorporate what he said.
i.e.
- Create 1 permanent table (e.g. tblArticlePosition)
- At the end of each week, store the week's article positions in this table
- Then, change the procedure I gave to query this table to get the previous week's positions and generate the current week's data on-the-fly.
- This will mean you don't have to work out 2 week's worth of positions each time the web page is accessed so should increase speed/performance of the query.
0

Expert Comment

ID: 8027222
"prior week MUST remain EXACTLY as it was reported then"

And it will. Records of views will not change. If they do change then surely there is a good reason for this and the top 10 should change as well.
0

LVL 69

Expert Comment

ID: 8027483
Top Ten Record/Movie, etc., lists, once published, almost never change (I don't recall ever seeing it happen).  It would be too confusing if they did.  I suspect that there might sometimes be corrections to albums/tickets sold, etc., after the first list is published, but the original list remains as it was, and the current list accurtely reflects in which position that album or movie or whatever appeared on the prior week's list.

So, for example, when I view the "Weekend Top Box Office" on Yahoo, it comes up instantly because it's been pre-totalled and saved.  Sure they could derive it again, but that would be a terrible waste of resources.  It's cheaper overall simply to keep a permanent store of the totals.

Pre-determining and storing such info is somewhat like a form of OLAP reporting, for which pre-defined totals are also pre-accumulated and stored to speed up future queries of the data.  Yes, the total could be re-calculated, hundreds of times I guess if necessary, but that's not very practical.
0

Expert Comment

ID: 8027547
That's tradition, and music tradition at that. This aint music top 10 and there's no reason to stick to that tradition.

Unless he wants too, his call.

But even then could have a status on the data row so that instead of deleting you change it to "inactive" and have your top 10 routine count inactive ones toward the total still.
0

LVL 69

Expert Comment

ID: 8027598
Certainly he could.  I just don't think he should.

Yahoo could also re-add all movie receipts everytime someone requested a list of the top movies for the past week/weekend.  But that would take so long that most people, including me, would probably quick asking for it because it took too long to get.
0

Expert Comment

ID: 8027730
Speed is a good point.

Maybe the lists could be reconstructed on a scheduled basis?
0

LVL 69

Expert Comment

ID: 8027829
The previous week's list is, in my view, static.

Obviously we aren't going to agree on this, so I'm going to quit taking up space in this question on this topic.  Everyone should have enough info by now to decide for themselves which approach they prefer.
0

Expert Comment

ID: 8027940
Well, the previous week is only as static as the data with which it was constructed.

That should be static. If it's not, why not? If the why not is a really good reason then that reason should be good enough that the top 10 also needs ammending.

But yeah, enough of his space. :) If you'd like to bitch at me tho feel free at bitch@panimu.com *huggles*
0

Author Comment

ID: 8032285
"Speed is a good point.

Maybe the lists could be reconstructed on a scheduled basis? "

would there be a way to create two tables in my db (say every sunday at 9pm - i dont know if sheduling is possible) 'lastweek' and 'thisweek' and just pull the data from there) and every week theyd automatically be updated?

thanks!
0

LVL 23

Expert Comment

ID: 8032335
You would only want to store Last Weeks positions in a permanent table. The current week's positions must be derived on-the-fly as they will be changing.

Assuming you *only* want to store the previous week's positions (i.e. dont want 2 weeks previous details):

To create the table:
CREATE TABLE tblPreviousPositions
(
Position INTEGER IDENTITY(1,1),
ArticleId INTEGER
)

Stored procedure to be scheduled weekly to update permanent table:
CREATE PROCEDURE stp_RefreshPreviousPositions
AS
BEGIN
DELETE FROM tblPreviousPositions
DBCC CHECKIDENT(tblPreviousPositions, RESEED, 1)

INSERT tblPreviousPositions(articleid)
SELECT TOP 10 articleid
FROM articlelog
WHERE DATEDIFF(wk, [date], GETDATE()) = 1
GROUP BY articleid
ORDER BY COUNT(articleid) DESC, articleid ASC
END

You can then package a call to this stored procedure into a DTS package. Once you have created the DTS package, simply schedule it to run once a week, every sunday at 9pm.

0

LVL 23

Expert Comment

ID: 8032342
This would mean your query would be:

CREATE TABLE #ptblThisWeek
(
position INTEGER IDENTITY(1,1),
articleid INTEGER
)

-- Get this week's TOP 10
INSERT #ptblThisWeek (articleid)
SELECT TOP 10 articleid
FROM articlelog
WHERE DATEDIFF(wk, [date], GETDATE()) = 0
GROUP BY articleid
ORDER BY COUNT(articleid) DESC, articleid ASC

SELECT tw.position "ThisWeek",
ISNULL((SELECT CAST(lw.position AS VARCHAR)FROM tblPreviousPositions lw WHERE lw.articleid = tw.articleid), '/') "LastWeek",
tw.articleid
FROM #ptblThisWeek tw

DROP TABLE #ptblThisWeek
0

Author Comment

ID: 8032422
"You can then package a call to this stored procedure into a DTS package. Once you have created the DTS package, simply schedule it to run once a week, every sunday at 9pm."

How would I do this?

Thanks again
0

LVL 23

Expert Comment

ID: 8032485
1) In Enterprise Manager, right click the Data Transformation Services folder and click "New Package...".
This will take you into design mode where you set up the package.

2)You need to add a connection to your database. Do this by clicking the icon in the side toolbar on the left of the screen that looks like a server (if you hover the mouse over it, it says "Microsoft OLE DB Provider For SQL Server"). Then enter your server, database and username/password details in the box that appears. Give the connection a name (e.g. "DatabaseName") - this goes in the first box in the popup window. Click Ok.

3)Now click the icon in the side tool bar that looks like a cylinder with 2 red arrows in a circle (tooltip says "Execute SQL Task"). In the window that pops up, enter details for the connection to use, description of the task, and in the SQL Statement box enter the call to your stored proc e.g. EXECUTE stp_RefreshPreviousPositions.
Click Ok.

4) Save the package. In Enterprise Manager, right click your package (in the Local Packages section) and choose "Schedule Package...". This will display a screen to take you through scheduling it.

Cheers
0

LVL 69

Expert Comment

ID: 8035409

To confirm, yes I was only referring to the prior week's list as permanent, up until whatever point the current week's list becomes "final".  For some brief interim period, it's possible that both would be pulled from pre-totalled data.
0

Expert Comment

ID: 9276361
amoran:
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE sâ€¦
###### Suggested Courses
Course of the Month10 days, 1 hour left to enroll