#Training: introduction to SQL

### Check first rows

SELECT *
FROM analytics
LIMIT 5

### Check first rows

SELECT *
FROM countries
LIMIT 5

### ✅ 1.1. Select all rows from table

SELECT *
FROM analytics

### ✅ 1.2. Show first 5 rows

SELECT *
FROM analytics
LIMIT 5

### ✅ 1.3. Show only visitor_id, date, channel for first 10 rows

SELECT
    visitor_id,
    date,
    channel
FROM analytics
LIMIT 10

### ✅ 2.1. Select rows with more than 100 pageviews

SELECT *
FROM analytics
WHERE pageviews > 100

### ✅ 2.2. Select 10 visits with exactly 2 transactions

SELECT *
FROM analytics
WHERE transactions = 2
LIMIT 10

### ✅ 2.3. Select visits with 3 to 5 transactions

SELECT *
FROM analytics
WHERE transactions BETWEEN 3 AND 5

### ✅ 3.1. Show number of rows
### Hint: use COUNT()

SELECT COUNT(*)
FROM analytics

### ✅ 3.2. Show sum of visits, transactions, and revenue
### Hint: use SUM()

SELECT
    SUM(visits),
    SUM(transactions),
    SUM(revenue)
FROM analytics

### ✅ 3.3. Show sum of visits, transactions, and revenue, and rename the columns properly
### Hint: use AS

SELECT
    SUM(visits) AS visits,
    SUM(transactions) AS transactions,
    SUM(revenue) AS revenue
FROM analytics

### ✅ 3.4. Show first and last date, and rename them

SELECT
    MIN(date) AS min_date,
    MAX(date) AS max_date
FROM analytics

### ✅ 3.5. Show conversion rate overall (transactions / visits)

SELECT
    SUM(transactions) / SUM(visits) AS conv_rate
FROM analytics

### ✅ 3.6. Show conversion rate between 1st and 10 May

SELECT
    SUM(transactions) / SUM(visits) AS conv_rate
FROM analytics
WHERE date BETWEEN '2017-05-01' AND '2017-05-10'

### ✅ 3.7. Show conversion rate on week-ends
### Hint: use DATE_PART('ISODOW', date) to get the weekday

SELECT
    SUM(transactions) / SUM(visits) AS conv_rate
FROM analytics
WHERE DATE_PART('ISODOW', date) > 5

### ✅ 3.8. Show number of transactions, total revenue and avg basket
### for 'Organic Search' between 10/05 and 15/05

SELECT
    SUM(transactions) AS transactions,
    SUM(revenue) AS revenue,
    SUM(revenue) / SUM(transactions) AS avg_basket
FROM analytics
WHERE
    channel = 'Organic Search'
    AND date BETWEEN '2017-05-10' AND '2017-05-15'

### ✅ 4.1. Show number of visits per day

SELECT
    date,
    SUM(visits)
FROM analytics
GROUP BY 1

### ✅ 4.2. Show number of visits per day, ordered by day, for May 1 to 10

SELECT
    date,
    SUM(visits) AS visits
FROM analytics
WHERE date BETWEEN '2017-05-01' AND '2017-05-10'
GROUP BY 1
ORDER BY 1

### ✅ 4.3. Show revenue per day, ordered by revenue descending, for top 5 days

SELECT
    date,
    SUM(revenue) AS revenue
FROM analytics
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5

### ✅ 4.4. Show distribution of number of transactions
### i.e. how many visits with 1, 2, 3... etc. transactions

SELECT
    transactions,
    COUNT(*)
FROM analytics
GROUP BY 1
ORDER BY 1

### ✅ 4.5. Show visits and pageviews per visit by channel, ordered by pages per visit descending

SELECT
    channel,
    SUM(visits) AS visits,
    SUM(pageviews) / SUM(visits) AS pageviews_per_visit
FROM analytics
GROUP BY 1
ORDER BY 3 DESC

### ✅ 5.1. Show first 5 rows for `country` table

SELECT *
FROM countries
LIMIT 5

### ✅ 5.2. Join `analytics` table with `countries` table for 10 rows

SELECT *
FROM analytics
LEFT JOIN countries
ON analytics.country = countries.country
LIMIT 10

### ✅ 5.3. Join `analytics` with `countries` using `USING`

SELECT *
FROM analytics
LEFT JOIN countries
USING (country)
LIMIT 10

### ✅ 6.1. Visits by day vs visits on previous day

SELECT
    date,
    visits,
    LAG(visits) OVER (ORDER BY date) AS prev_visits
FROM (
    SELECT
        date,
        SUM(visits) AS visits
    FROM analytics
    GROUP BY 1
) AS sub1
ORDER BY date

SELECT
    channel,
    ROUND(SUM(pageviews) / SUM(visits), 2) AS pageviews_per_visit
FROM analytics
GROUP BY 1
ORDER BY 1 DESC

SELECT
    AVG(visits) AS avg_visits_1,
    SUM(visits) / COUNT(date) AS avg_visits_2
FROM (
    SELECT
        date,
        SUM(visits) AS visits
    FROM analytics
    GROUP BY 1
) AS sub1

SELECT *
FROM (
    SELECT
        country,
        SUM(visits) AS visits,
        SUM(revenue) AS revenue,
        SUM(transactions) / SUM(visits) AS conv_rate
    FROM analytics
    GROUP BY 1
) AS sub1
LEFT JOIN countries
USING (country)
WHERE revenue > 0
ORDER BY revenue DESC

SELECT
    date,
    visits,
    LEAD(visits) OVER (ORDER BY date) AS prev_visits,
    visits - LAG(visits) OVER (ORDER BY date) AS daily_var,
    (visits - LAG(visits) OVER (ORDER BY date)) / (LAG(visits) OVER (ORDER BY date)) AS daily_var_pct
FROM (
    SELECT
        date,
        SUM(visits) AS visits
    FROM analytics
    GROUP BY 1
) AS sub1
ORDER BY date