Revenue Analysis with Date/Time Functions

from google.cloud import bigquery
import pandas as pd
 
# Authentication setup (when using service account key)
# import os
# os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path/to/your-key.json"
 
# Create BigQuery client
client = bigquery.Client(project='your-project-id')
 
# Query execution function
def run_query(query):
    return client.query(query).to_dataframe()

-- Get current date/time
CURRENT_DATE()        -- Current date
CURRENT_DATETIME()    -- Current datetime
CURRENT_TIMESTAMP()   -- Current timestamp
 
-- Type conversion
CAST(timestamp_col AS DATE)      -- TIMESTAMP → DATE
CAST(timestamp_col AS DATETIME)  -- TIMESTAMP → DATETIME

SELECT
    CURRENT_DATE() AS current_date,
    CURRENT_DATETIME() AS current_datetime,
    CURRENT_TIMESTAMP() AS current_timestamp,
 
    -- Convert created_at to each type
    CAST(created_at AS DATE) AS order_date,
    CAST(created_at AS DATETIME) AS order_datetime,
    created_at AS order_timestamp
FROM `your-project-id.thelook_ecommerce.orders`
LIMIT 5

EXTRACT(part FROM date_expression)

SELECT
    EXTRACT(DAYOFWEEK FROM created_at) AS day_of_week,
    CASE EXTRACT(DAYOFWEEK FROM created_at)
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END AS day_name,
    COUNT(*) AS order_count
FROM `your-project-id.thelook_ecommerce.orders`
GROUP BY day_of_week, day_name
ORDER BY day_of_week

DATE_TRUNC(date_expression, date_part)

SELECT
    DATE_TRUNC(CAST(o.created_at AS DATE), MONTH) AS month,
    COUNT(DISTINCT o.order_id) AS order_count,
    COUNT(DISTINCT o.user_id) AS customer_count,
    SUM(oi.sale_price) AS total_revenue,
    ROUND(SUM(oi.sale_price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM `your-project-id.thelook_ecommerce.orders` o
JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
WHERE o.created_at >= '2024-01-01'
GROUP BY month
ORDER BY month

DATE_DIFF(end_date, start_date, date_part)

SELECT
    user_id,
    MIN(CAST(created_at AS DATE)) AS first_order_date,
    MAX(CAST(created_at AS DATE)) AS last_order_date,
    DATE_DIFF(MAX(CAST(created_at AS DATE)), MIN(CAST(created_at AS DATE)), DAY) AS days_active,
    COUNT(DISTINCT order_id) AS total_orders,
    ROUND(
        COUNT(DISTINCT order_id) * 1.0 /
        NULLIF(DATE_DIFF(MAX(CAST(created_at AS DATE)), MIN(CAST(created_at AS DATE)), MONTH), 0),
        2
    ) AS orders_per_month
FROM `your-project-id.thelook_ecommerce.orders`
GROUP BY user_id
HAVING COUNT(DISTINCT order_id) > 1  -- Only customers with 2+ orders
ORDER BY days_active DESC
LIMIT 10

-- Previous month data join
DATE_SUB(current_month, INTERVAL 1 MONTH)
 
-- Same month last year data join
DATE_SUB(current_month, INTERVAL 1 YEAR)

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC(CAST(o.created_at AS DATE), MONTH) AS month,
        SUM(oi.sale_price) AS revenue
    FROM `your-project-id.thelook_ecommerce.orders` o
    JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
    GROUP BY month
)
SELECT
    curr.month,
    curr.revenue AS current_revenue,
    prev.revenue AS previous_revenue,
    ROUND((curr.revenue - prev.revenue) / prev.revenue * 100, 2) AS mom_growth_pct
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev
    ON curr.month = DATE_ADD(prev.month, INTERVAL 1 MONTH)
WHERE curr.month >= '2024-01-01'
ORDER BY curr.month