Write SQL for session analytics

You are given two tables for an e-commerce product.

Tables

1) shops

A shop dimension table that contains duplicate rows for the same shop_id.

Columns:

• shop_id (INT)
• shop_name (STRING)
• created_at (TIMESTAMP)

Notes:

• shops may contain multiple rows per shop_id .

2) sessions

A session fact table.

Columns:

• session_id (STRING)
• shop_id (INT)
• started_at (TIMESTAMP)
• url (STRING)

Assume:

• A “daily” metric is based on DATE(started_at) .
• You may assume a reasonable reporting window exists in the data (or you can derive it from sessions ).

Tasks (write one SQL query per task)

1. Daily total sessions
   • Return one row per day with the total number of sessions.
   • Output: day , session_cnt .
2. Daily sessions by shop
   • Return one row per day per shop.
   • Because shops has duplicates, you must deduplicate it before joining .
   • Output: day , shop_id , shop_name , session_cnt .
3. Daily sessions by shop with a 7-day rolling average (excluding today)
   • For each shop and each calendar day in the reporting window, return:
     • day
     • shop_id
     • session_cnt (0 if there were no sessions that day)
     • rolling_7d_avg_excl_today : average of the previous 7 days’ session_cnt , i.e. days [day-7, day-1] .
   • Requirements:
     • You must generate a calendar so that missing days appear.
     • Days with no sessions must be treated as 0 and included in the rolling average.
4. Extract information from a URL
   • From sessions.url , extract the value of the query parameter utm_campaign .
   • Examples:
     • https://a.com/?utm_source=google&utm_campaign=summer_sale → summer_sale
     • https://a.com/path?x=1 → NULL
   • Output: session_id , utm_campaign .

Use standard SQL (any common dialect is fine), and ensure each query can be run independently.