-- SQL — read, filter, and join data
-- A very common analyst query: last 30 days of orders
-- with the customer name attached.

SELECT
  o.order_id,
  o.created_at,
  c.name      AS customer,
  o.total
FROM orders o
JOIN customers c
  ON c.id = o.customer_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 100;

-- Key ideas:
-- - JOIN combines rows across tables on a key
-- - WHERE filters rows before grouping
-- - ORDER BY + LIMIT shape the result set

-- SQL — group by and aggregate
-- Monthly revenue per product category, 2026.

SELECT
  DATE_TRUNC('month', o.created_at) AS month,
  p.category,
  COUNT(DISTINCT o.order_id)        AS orders,
  SUM(oi.qty * oi.unit_price)       AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id       = oi.product_id
WHERE o.created_at >= '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, revenue DESC;

-- Key ideas:
-- - GROUP BY collapses rows into groups
-- - Aggregates (COUNT, SUM) produce one value per group
-- - DATE_TRUNC is the analyst's best friend

-- SQL — window functions
-- Rank products by revenue within each category.

WITH product_revenue AS (
  SELECT
    p.category,
    p.name,
    SUM(oi.qty * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category, p.name
)
SELECT
  category,
  name,
  revenue,
  RANK() OVER (
    PARTITION BY category
    ORDER BY revenue DESC
  ) AS rank_in_category
FROM product_revenue
ORDER BY category, rank_in_category;

-- Window functions are how you compare rows
-- to their peers without collapsing them.
-- They're tested in most analyst interviews.

# Playwright (Python) — a first browser automation script
from playwright.sync_api import sync_playwright

with sync_playwright() as p:
    browser = p.chromium.launch(headless=True)
    page = browser.new_page()

    page.goto("https://saucedemo.com")

    page.get_by_placeholder("Username").fill("standard_user")
    page.get_by_placeholder("Password").fill("secret_sauce")
    page.get_by_role("button", name="Login").click()

    # Auto-waits until the inventory page loads
    page.wait_for_url("**/inventory.html")
    print("Title:", page.title())

    browser.close()

# Note: Playwright auto-waits for elements by default —
# that's why there are no explicit sleeps or waits here.

# Playwright (Python) — scrape a table into a list
from playwright.sync_api import sync_playwright

with sync_playwright() as p:
    browser = p.chromium.launch(headless=True)
    page = browser.new_page()

    page.goto("https://example.com/products")
    page.wait_for_selector("table.data")

    rows = []
    for tr in page.locator("table.data tbody tr").all():
        cells = [c.inner_text().strip() for c in tr.locator("td").all()]
        if cells:
            rows.append(cells)

    print(f"Scraped {len(rows)} rows")
    print(rows[:3])

    browser.close()

# Always check the site's terms of service and robots.txt
# before scraping. Prefer official APIs where they exist.

# Playwright + Pytest — a proper E2E test
# tests/test_login.py

import re
import pytest
from playwright.sync_api import Page, expect

@pytest.fixture
def app_url() -> str:
    return "https://saucedemo.com"

def test_login_succeeds(page: Page, app_url: str) -> None:
    page.goto(app_url)

    page.get_by_placeholder("Username").fill("standard_user")
    page.get_by_placeholder("Password").fill("secret_sauce")
    page.get_by_role("button", name="Login").click()

    expect(page).to_have_url(re.compile("/inventory"))
    expect(page.locator(".title")).to_have_text("Products")

def test_login_fails_with_wrong_password(page: Page, app_url: str) -> None:
    page.goto(app_url)

    page.get_by_placeholder("Username").fill("standard_user")
    page.get_by_placeholder("Password").fill("wrong")
    page.get_by_role("button", name="Login").click()

    expect(page.locator("[data-test='error']")) \
        .to_contain_text("Username and password do not match")

# Run: pytest --headed   (or without --headed for headless)
# In CI:  pytest --tracing on
# Then:   playwright show-trace test-results/<test>/trace.zip

SQL — 30-day beginner plan:
Week 1: SELECT, WHERE, ORDER BY, LIMIT
Week 2: JOINs (INNER, LEFT), aggregates
Week 3: GROUP BY, HAVING, subqueries, CASE
Week 4: Window functions + a real analysis
        on a public dataset (Kaggle / data.gov.in)

Result: Interview-ready for analyst roles.
Total effort: ~30 hours.

Playwright — 30-day beginner plan:
(assumes Python or JS basics)

Week 1: Install, browsers, first navigation
Week 2: Locators, assertions, Pytest setup
Week 3: Fixtures, traces, page objects
Week 4: A real E2E suite + GitHub Actions CI

Result: Interview-ready for junior QA roles.
Total effort: ~30 hours.

Not recommended without:
- Python or JavaScript fundamentals
- Basic Git (to save and share your tests)
- One deployed target to test against

Data / backend path (recommended order):
1. SQL                   (4-6 weeks)
2. Excel / spreadsheets  (2-3 weeks)
3. Python + Pandas       (4-8 weeks)
4. Power BI or Tableau   (3-4 weeks)
5. (Optional) dbt, cloud warehouse

Roles: Data Analyst, BI Developer, Backend Developer,
       Data Engineer (with more depth)

QA / automation path (recommended order):
1. Python or JavaScript basics (4-8 weeks)
2. SQL                          (2-4 weeks)
3. Playwright                   (2-4 weeks)
4. Pytest + Git                 (2 weeks)
5. CI/CD (GitHub Actions)       (1-2 weeks)
6. API testing (Postman)        (2 weeks)

Roles: QA Engineer, SDET, Automation Tester,
       Front-end Developer with strong testing

Note: QA engineers use SQL too — to verify
      that UI actions persist data correctly.
      So SQL ends up on both paths.