SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column
LIMIT 10;

SELECT 
    GenreId,
    COUNT(*) AS track_count,
    AVG(Milliseconds)/60000 AS avg_min
FROM Track
GROUP BY GenreId
HAVING COUNT(*) > 100
ORDER BY track_count DESC;

SELECT 
    c.FirstName || ' ' || c.LastName AS customer,
    SUM(i.Total) AS total_spent
FROM Customer c
LEFT JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId
ORDER BY total_spent DESC
LIMIT 5;

SELECT Name, Composer
FROM Track
WHERE GenreId = (
    SELECT GenreId FROM Genre WHERE Name = 'Rock'
);

WITH rock_tracks AS (
    SELECT * FROM Track 
    WHERE GenreId = (SELECT GenreId FROM Genre WHERE Name = 'Rock')
)
SELECT Composer, COUNT(*) 
FROM rock_tracks 
GROUP BY Composer 
ORDER BY COUNT(*) DESC;

-- Rank tracks by length per genre
SELECT 
    Name,
    GenreId,
    Milliseconds,
    RANK() OVER (PARTITION BY GenreId ORDER BY Milliseconds DESC) AS rank_longest
FROM Track;

WITH customer_spend AS (
    SELECT 
        c.CustomerId,
        c.Country,
        SUM(i.Total) AS spend
    FROM Customer c
    JOIN Invoice i ON c.CustomerId = i.CustomerId
    GROUP BY c.CustomerId
),
country_total AS (
    SELECT Country, SUM(spend) AS country_spend
    FROM customer_spend
    GROUP BY Country
)
SELECT 
    cs.CustomerId,
    cs.Country,
    cs.spend,
    ROUND(100.0 * cs.spend / ct.country_spend, 2) AS pct_of_country
FROM customer_spend cs
JOIN country_total ct ON cs.Country = ct.Country
ORDER BY pct_of_country DESC;

sql-music-analysis/
├── queries/
│   ├── 01_top_customers.sql
│   ├── 02_genre_performance.sql
│   ├── 03_employee_sales.sql
│   └── report.md
├── data/
│   └── chinook.db
└── README.md

# Music Store SQL Analysis

## Key Insights
- **Rock** is the top genre (54% of sales)
- Top customer: **Helena Holý** ($49.62 from Czech Republic)
- **Jane Peacock** is the top-performing employee
- 2 tracks have **never been sold**

## How to Run
```sql
-- Open in DB Browser for SQLite
-- Run queries/01_top_customers.sql

---

## Interview-Ready SQL (LeetCode Style)

| Difficulty | Problem | Link |
|----------|-------|------|
| Easy | [175. Combine Two Tables](https://leetcode.com/problems/combine-two-tables/) | `LEFT JOIN` |
| Easy | [181. Employees Earning More Than Managers](https://leetcode.com/problems/employees-earning-more-than-their-managers/) | Self-join |
| Medium | [176. Second Highest Salary](https://leetcode.com/problems/second-highest-salary/) | `LIMIT OFFSET` |
| Medium | [177. Nth Highest Salary](https://leetcode.com/problems/nth-highest-salary/) | CTE + `DENSE_RANK()` |
| Hard | [185. Department Top Three Salaries](https://leetcode.com/problems/department-top-three-salaries/) | Window + Filter |

**Goal**: Solve **50 LeetCode SQL problems** (focus on **Easy 20 + Medium 30**)

---

## SQL Cheat Sheet (Copy-Paste)

```sql
-- SELECT
SELECT *, ROUND(col, 2)
FROM table
WHERE col IS NOT NULL
  AND col LIKE '%pattern%'
  AND col IN (1,2,3)
ORDER BY col DESC
LIMIT 10;

-- AGGREGATION
SELECT category, 
       COUNT(*), 
       SUM(sales), 
       AVG(price),
       MAX(date)
FROM table
GROUP BY category
HAVING COUNT(*) > 5;

-- JOINS
SELECT *
FROM A
INNER JOIN B ON A.id = B.id
LEFT JOIN C ON B.id = C.id;

-- WINDOW
SELECT 
    col,
    ROW_NUMBER() OVER (PARTITION BY group ORDER BY val) AS rn,
    RANK() OVER (PARTITION BY group ORDER BY val DESC) AS rank
FROM table;

-- CTE
WITH cte AS (SELECT ...) 
SELECT * FROM cte WHERE ...;

SELECT Name, Milliseconds/60000.0 AS minutes 
FROM Track 
ORDER BY minutes DESC 
LIMIT 1;