Q:

How do I query previous rows? 

I have a page audit table that records which pages a user has accessed.  Given an specific page, I need to find what previous page the user has accessed and what was the most accessed.  
For example, the FAQ Page_ID is 3.  I want to know if it is more frequently accessed from the First Access page (ID 1) or Home page (ID 5). 
Example:
Page Audit Table (SQL Server)
ID  | Page_ID | User_ID
1   | 1       | 6
2   | 3       | 6
3   | 5       | 4
4   | 3       | 4
5   | 1       | 7
6   | 3       | 7
7   | 1       | 5
8   | 3       | 2        --Note: previous user is not 2
9   | 3       | 5        --Note: previous page for user 5 is 1 and not 3

Looking for Page_ID = 3, I want to retrieve:
Previous Page | Count
1             | 3
5             | 1

Note: I've looked some similar questions here (like that one), but it didn't help me to solve this problem. 

A:

You can use window functions as one way to figure this out:
with UserPage as (
  select
    User_ID,
    Page_ID,
    row_number() over (partition by User_ID order by ID) as rn
  from
    PageAudit
)
select
  p1.Page_ID,
  count(*)
from
  UserPage p1
    inner join
  UserPage p2
    on p1.User_ID = p2.User_ID and
       p1.rn + 1 = p2.rn
where
  p2.Page_ID = 3
group by
  p1.Page_ID;

SQLFiddle Demo
If you have SQL2012, the answers using lag will be a lot more efficient.  This one works on SQL2008 too. 
For reference, as I think one of the lag solutions is over complicated, and one is wrong:
with prev as (
  select
    page_id,
    lag(page_id,1) over (partition by user_id order by id) as prev_page
  from 
    PageAudit
)
select
  prev_page,
  count(*)
from
  prev
where
  page_id = 3 and
  prev_page is not null -- people who landed on page 3 without a previous page
group by 
  prev_page

SQLFiddle Example of Lag

