Here's the sql you want:
select coalesce(u.Username, 'Anonymous') as UserName
, TabPath as TabPath
, count(*) as PageViews
from dnn_SiteLog l
left join dnn_Users u
on u.UserId = l.UserId
inner join dnn_Tabs t
on t.TabId = l.TabId
where DateTime between '2008-07-01' and '2008-07-31'
group by u.UserName, t.TabPath
order by 1,2
You'll have to replace the dnn_ with either a different object qualifier, or remove it if you don't use an object qualifier on your site. Ie, dnn_Tabs should be Tabs if you don't have an object qualifier in your web.config.
Change the datetime values to filter between different dates. If your site is large, I wouldn't recommend running with too large a gap between the date fields, or you'll probably timeout your database. You can also add other columns to this query from the user, tabs and sitelog tables. The SiteLog table is where the detail is stored, as long as you have the 'track visits' options switched on in your host settings, and your site settings.
Incidentally, you shouldn't talk about 'hits' when discussing web stats. A 'hit' is just a record of a resource being pulled from a server. It is better to talk of 'Page Views' instead.