title: BIG QUERY SQL: How to find different sets of duplicates within a visit with same unique keys but different period of the visit?
body: <p><strong>WHAT IS THIS POST FOR?</strong></p>
  
  <p>In bigquery, I need to remove duplicated rows with a <strong>caveat</strong> </p>
  
  <p><em>that duplication happened within the same visit for a visitor for page visits with the same pagename.</em> </p>
  
  <p>GROUP BY DOES NOT RESOLVE THE ISSUE</p>
  
  <p>Below,I have explained the data, issue, possible resolution and measure taken to the best of my ability.</p>
  
  <p><strong>WHAT DATA AM I USING?</strong></p>
  
  <p>Adobe data imported to Bigquery ( no issues in importing )</p>
  
  <p>Each row is the data collected by page-view for a visitor.</p>
  
  <p>Each time a visitor moves to new page it counts a visit_page_num + 1 in the same visit_num and for same visitor_id.</p>
  
  <p>The pagename is recorded for each page visited.</p>
  
  <p><strong>ISSUE :</strong></p>
  
  <p>In this image,</p>
  
  <p><a href="https://i.stack.imgur.com/0G1WI.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0G1WI.jpg" alt="enter image description here"></a></p>
  
  <p>some of the visit_page_num are <strong>counted as unique</strong> on unique visitor_id, visit_num but are <strong>duplicated</strong> as the pagename is same.</p>
  
  <pre><code>ISSUE
  visit_page_num      pagename
         1               a
         2               b
         3               c  -issue
         4               c  -issue
         5               d
         6               d
         7               d
         8               e
         9               c  -issue
        10               c  -issue
        11               c  -issue 
  
  
  Solution so far with GROUP BY
  visit_page_num      pagename
         1               a
         2               b
         3               c  -issue
         5               d
         8               e
  
  
    GOAL
  visit_page_num      pagename
         1               a
         2               b
         3               c  -Goal
         5               d
         8               e
         9               c  -Goal
  </code></pre>
  
  <p>When duplicates on same pagename occur but at different time in the visit then how <strong><em>do we insure the later duplication are not eliminated but counted as different page visit</em></strong>.  </p>
  
  <p><strong>QUERY USED :</strong></p>
  
  <pre><code>SELECT visitor_id
  ,visit_num
  ,pagename
  ,first (visit_page_num) AS first
  ,ROW_NUMBER() OVER(PARTITION BY visitor_id, visit_num ORDER BY visitor_id, visit_num, pagename) AS int_var
  FROM [table] 
  GROUP BY visitor_id, visit_num, pagename
  ORDER BY visitor_id, visit_num, first
  </code></pre>
  
  <p><strong>OUTPUT :</strong></p>
  
  <p>Everything is Good <strong>EXCEPT</strong> the,</p>
  
  <p><a href="https://i.stack.imgur.com/7CjJC.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7CjJC.jpg" alt="enter image description here"></a></p>
  
  <p>visitor_id = A on visit_num = 1 and pagename = c</p>
  
  <p><strong>ACTIONS TAKEN :</strong></p>
  
  <ol>
  <li><p>I have tried the LEAD and LAG function with MIN and MAX function in second step>>> SAME OUTPUT</p></li>
  <li><p>CHECKED the web, normal SQL functions that can be translated into BIGQUERY >> SAME OUTPUT</p></li>
  <li><p>Asked my team lead >> SAME OUTPUT</p></li>
  <li><p>5 hours of experimenting >> SAME OUTPUT</p></li>
  </ol>
  
  <p><strong>CAVEAT</strong> </p>
  
  <p>Cannot use field_date OR any time field OR any other field beside the one mentioned in the table</p>