HARD

WITH mentor AS (
    SELECT
        hr.employees.employee_id,
        hr.employees.job_id,
        hr.employees.hire_date,
        ROW_NUMBER() OVER (
            PARTITION BY hr.employees.job_id
            ORDER BY hr.employees.hire_date
        ) AS hire_date_sequence_asc
    FROM
        hr.employees
),
mentee AS (
    SELECT
        hr.employees.employee_id,
        hr.employees.job_id,
        hr.employees.hire_date,
        ROW_NUMBER() OVER (
            PARTITION BY hr.employees.job_id
            ORDER BY hr.employees.hire_date DESC
        ) AS hire_date_sequence_desc
    FROM
        hr.employees
)
SELECT
    mentor.employee_id AS mentor_id,
    mentor.job_id AS mentor_job_id,
    mentor.hire_date AS mentor_hire_date,
    mentee.employee_id AS mentee_id,
    mentee.job_id AS mentee_job_id,
    mentee.hire_date AS mentee_hire_date
FROM
    mentor
    INNER JOIN
    mentee
        ON mentor.job_id = mentee.job_id
    	AND mentor.employee_id != mentee.employee_id
        AND mentor.hire_date_sequence_asc = 1
        AND mentee.hire_date_sequence_desc = 1
ORDER BY
    mentor.job_id;
 
-- Solution 2
SELECT
    mentor.employee_id AS mentor_id,
    mentor.job_id AS mentor_job_id,
    mentor.hire_date AS mentor_hire_date,
    mentee.employee_id AS mentee_id,
    mentee.job_id AS mentee_job_id,
    mentee.hire_date AS mentee_hire_date
FROM (
    SELECT
        hr.employees.employee_id,
        hr.employees.job_id,
        hr.employees.hire_date,
        ROW_NUMBER() OVER (
            PARTITION BY hr.employees.job_id
            ORDER BY hr.employees.hire_date
        ) AS hire_date_sequence_asc
    FROM
        hr.employees
    ) mentor
    INNER JOIN (
    SELECT
        hr.employees.employee_id,
        hr.employees.job_id,
        hr.employees.hire_date,
        ROW_NUMBER() OVER (
            PARTITION BY hr.employees.job_id
            ORDER BY hr.employees.hire_date DESC
        ) AS hire_date_sequence_desc
    FROM
        hr.employees
    ) mentee
        ON mentor.job_id = mentee.job_id
    	AND mentor.employee_id != mentee.employee_id
        AND mentor.hire_date_sequence_asc = 1
        AND mentee.hire_date_sequence_desc = 1
ORDER BY
    mentor.job_id;

WITH applicable_job AS (
	SELECT
		hr.employees.job_id,
    	hr.jobs.job_title
	FROM
		hr.employees
		INNER JOIN
		hr.jobs
			ON hr.employees.job_id = hr.jobs.job_id
	GROUP BY
		hr.employees.job_id,
    	hr.jobs.job_title
	HAVING
		COUNT(hr.employees.employee_id) > 1
)
SELECT
	hr.employees.employee_id,
	hr.employees.last_name || ', ' || hr.employees.first_name AS employee_name,
	applicable_job.job_title,
	hr.employees.hire_date,	
	ROUND(MONTHS_BETWEEN(SYSDATE, hr.employees.hire_date) / 12, 2) AS employment_duration,
	hr.employees.salary,
	CASE
		WHEN LEAD(hr.employees.salary, 1, hr.employees.salary) OVER (
			PARTITION BY hr.employees.job_id
			ORDER BY hr.employees.hire_date, hr.employees.salary DESC
		) <= hr.employees.salary
			THEN 1
		ELSE 0
	END AS inconsistency_flag,
	DENSE_RANK() OVER (
		PARTITION BY hr.employees.job_id
		ORDER BY hr.employees.salary DESC
	) AS salary_sequence_desc
FROM
	hr.employees
	INNER JOIN
	applicable_job
		ON hr.employees.job_id = applicable_job.job_id
ORDER BY
    hr.employees.job_id,
    employment_duration DESC;

-- Solution 1
WITH single_order_customer AS (
    SELECT
        oe.orders.customer_id,
        COUNT(oe.orders.order_id) AS order_count
    FROM
        oe.orders
    GROUP BY
        oe.orders.customer_id
    HAVING 
        COUNT(oe.orders.order_id) = 1
)
SELECT
    oe.orders.customer_id,
    oe.orders.order_id,
    oe.orders.order_date,
    oe.order_items.line_item_id,
    oe.order_items.unit_price,
    oe.order_items.quantity,
    oe.order_items.unit_price * oe.order_items.quantity AS line_item_total,
    oe.orders.order_total
FROM
    oe.orders
    INNER JOIN
	single_order_customer
        ON oe.orders.customer_id = single_order_customer.customer_id
    INNER JOIN
	oe.order_items
    	ON oe.orders.order_id = oe.order_items.order_id
ORDER BY
    oe.orders.customer_id,
    oe.order_items.line_item_id;

-- Solution 2
SELECT
    oe.orders.customer_id,
    oe.orders.order_id,
    oe.orders.order_date,
    oe.order_items.line_item_id,
    oe.order_items.unit_price,
    oe.order_items.quantity,
    oe.order_items.unit_price * oe.order_items.quantity AS line_item_total,
    oe.orders.order_total
FROM
    oe.orders
    INNER JOIN (
	SELECT
		oe.orders.customer_id,
		COUNT(oe.orders.order_id) AS order_count
	FROM
		oe.orders
	GROUP BY
		oe.orders.customer_id
	HAVING 
		COUNT(oe.orders.order_id) = 1
	) single_order
        ON oe.orders.customer_id = single_order.customer_id
    INNER JOIN
	oe.order_items
    	ON oe.orders.order_id = oe.order_items.order_id
ORDER BY
    oe.orders.customer_id,
    oe.order_items.line_item_id;

-- Solution 1
WITH category_popularity AS (
    SELECT
    	oe.product_information.category_id,
        oe.categories_tab.category_description,
        COUNT(oe.product_information.category_id) AS order_count,
        DENSE_RANK() OVER (
            ORDER BY COUNT(oe.product_information.category_id)
        ) AS category_popularity_sequence_asc,
        DENSE_RANK() OVER (
            ORDER BY COUNT(oe.product_information.category_id) DESC
        ) AS category_popularity_sequence_desc
    FROM
    	oe.orders
        INNER JOIN
		oe.order_items
            ON oe.orders.order_id = oe.order_items.order_id
        INNER JOIN
		oe.product_information
            ON oe.order_items.product_id = oe.product_information.product_id
        INNER JOIN
		oe.categories_tab
            ON oe.product_information.category_id = oe.categories_tab.category_id
	WHERE
		oe.orders.order_date >= '01-JAN-07'
		AND oe.orders.order_date < '01-JAN-08'
    GROUP BY
    	oe.product_information.category_id,
    	oe.categories_tab.category_description
)
SELECT
    category_popularity.category_id,
	category_popularity.category_description,
	category_popularity.order_count
FROM
	category_popularity
WHERE
    category_popularity.category_popularity_sequence_asc <= 5
    OR category_popularity.category_popularity_sequence_desc <= 5
ORDER BY
    category_popularity.order_count DESC;

-- Solution 2
SELECT
    category_popularity.category_id,
	category_popularity.category_description,
	category_popularity.order_count
FROM (
    SELECT
    	oe.product_information.category_id,
        oe.categories_tab.category_description,
        COUNT(oe.product_information.category_id) AS order_count,
        DENSE_RANK() OVER (
            ORDER BY COUNT(oe.product_information.category_id)
        ) AS category_popularity_sequence_asc,
        DENSE_RANK() OVER (
            ORDER BY COUNT(oe.product_information.category_id) DESC
        ) AS category_popularity_sequence_desc
    FROM
    	oe.orders
        INNER JOIN
		oe.order_items
            ON oe.orders.order_id = oe.order_items.order_id
        INNER JOIN
		oe.product_information
            ON oe.order_items.product_id = oe.product_information.product_id
        INNER JOIN
		oe.categories_tab
            ON oe.product_information.category_id = oe.categories_tab.category_id
	WHERE
		oe.orders.order_date >= '01-JAN-07'
		AND oe.orders.order_date < '01-JAN-08'
    GROUP BY
    	oe.product_information.category_id,
    	oe.categories_tab.category_description
) category_popularity
WHERE
    category_popularity.category_popularity_sequence_asc <= 5
    OR category_popularity.category_popularity_sequence_desc <= 5
ORDER BY
    category_popularity.order_count DESC;

-- Solution 1
WITH yearly_mode_count AS (
	SELECT
		oe.orders.order_mode,
		EXTRACT(YEAR FROM oe.orders.order_date) AS order_year,
		COUNT(oe.orders.order_id) AS order_count
	FROM
		oe.orders
	WHERE
		oe.orders.order_mode = 'online'
	GROUP BY
		oe.orders.order_mode,
		EXTRACT(YEAR FROM oe.orders.order_date)
),
yearly_count AS (
	SELECT
		EXTRACT(YEAR FROM oe.orders.order_date) AS order_year,
		COUNT(oe.orders.order_id) AS order_count
	FROM
		oe.orders
	GROUP BY
		EXTRACT(YEAR FROM oe.orders.order_date)
)
SELECT
	yearly_mode_count.order_mode,
	yearly_mode_count.order_year,
	yearly_mode_count.order_count AS mode_count,
	yearly_count.order_count,
	ROUND((yearly_mode_count.order_count / yearly_count.order_count) * 100, 2) AS percent_of_total
FROM
	yearly_mode_count
	INNER JOIN
	yearly_count
		ON yearly_mode_count.order_year = yearly_count.order_year
ORDER BY
	yearly_mode_count.order_year;

-- Solution 2
SELECT
	yearly_mode_count.order_mode,
	yearly_mode_count.order_year,
	yearly_mode_count.order_count AS mode_count,
	yearly_count.order_count,
	ROUND((yearly_mode_count.order_count / yearly_count.order_count) * 100, 2) AS percent_of_total
FROM (
	SELECT
		oe.orders.order_mode,
		EXTRACT(YEAR FROM oe.orders.order_date) AS order_year,
		COUNT(oe.orders.order_id) AS order_count
	FROM
		oe.orders
	WHERE
		oe.orders.order_mode = 'online'
	GROUP BY
		oe.orders.order_mode,
		EXTRACT(YEAR FROM oe.orders.order_date)
	) yearly_mode_count
	INNER JOIN (
	SELECT
		EXTRACT(YEAR FROM oe.orders.order_date) AS order_year,
		COUNT(oe.orders.order_id) AS order_count
	FROM
		oe.orders
	GROUP BY
		EXTRACT(YEAR FROM oe.orders.order_date) 
	) yearly_count
		ON yearly_mode_count.order_year = yearly_count.order_year
ORDER BY
	yearly_mode_count.order_year;

-- Solution 1
WITH customer_spending AS (
	SELECT
		oe.customers.customer_id,
		oe.customers.cust_last_name || ', ' || oe.customers.cust_first_name AS customer_name,
		SUM(oe.orders.order_total) AS order_total,
		DENSE_RANK() OVER (ORDER BY SUM(oe.orders.order_total) DESC) AS order_total_sequence
    FROM
		oe.customers
		INNER JOIN
		oe.orders
			ON oe.customers.customer_id = oe.orders.customer_id
	WHERE
		oe.orders.order_date >= '01-JAN-08'
		AND oe.orders.order_date < '01-JAN-09'
    GROUP BY
		oe.customers.customer_id,
        oe.customers.cust_last_name || ', ' || oe.customers.cust_first_name
)
SELECT
    customer_spending.customer_id,
    customer_spending.customer_name,
    customer_spending.order_total,
	customer_spending.order_total_sequence
FROM
	customer_spending
WHERE
	customer_spending.order_total_sequence <= 5
ORDER BY
	customer_spending.order_total DESC;

-- Solution 2
SELECT
    customer_spending.customer_id,
    customer_spending.customer_name,
    customer_spending.order_total,
	customer_spending.order_total_sequence
FROM (
	SELECT
		oe.customers.customer_id,
		oe.customers.cust_last_name || ', ' || oe.customers.cust_first_name AS customer_name,
		SUM(oe.orders.order_total) AS order_total,
		DENSE_RANK() OVER (ORDER BY SUM(oe.orders.order_total) DESC) AS order_total_sequence
    FROM
		oe.customers
		INNER JOIN
		oe.orders
			ON oe.customers.customer_id = oe.orders.customer_id
	WHERE
		oe.orders.order_date >= '01-JAN-08'
		AND oe.orders.order_date < '01-JAN-09'
    GROUP BY
		oe.customers.customer_id,
        oe.customers.cust_last_name || ', ' || oe.customers.cust_first_name
) customer_spending
WHERE
	customer_spending.order_total_sequence <= 5
ORDER BY
	customer_spending.order_total DESC;

SELECT
	oe.product_information.supplier_id,
	COUNT(*) AS product_count
FROM
	oe.product_information
	INNER JOIN
	oe.categories_tab
		ON oe.product_information.category_id = oe.categories_tab.category_id
WHERE
	oe.product_information.supplier_id != '103092'
	AND oe.categories_tab.category_name LIKE 'software%'
GROUP BY
	oe.product_information.supplier_id
ORDER BY
	product_count DESC;

SELECT
    oe.orders.sales_rep_id,
    EXTRACT(YEAR FROM oe.orders.order_date) AS order_year,
    COUNT(*) AS order_count
FROM
    oe.orders
WHERE
    oe.orders.sales_rep_id IS NOT NULL
    AND oe.orders.order_date >= '01-JAN-07'
    AND oe.orders.order_date < '01-JAN-09'
GROUP BY
    oe.orders.sales_rep_id,
    EXTRACT(YEAR FROM oe.orders.order_date)
ORDER BY
    order_year,
    order_count,
    oe.orders.sales_rep_id;

WITH product_category_performance AS (
    SELECT
        TRUNC(north_america_sale.report_date, 'MONTH') AS reporting_month,
        DENSE_RANK() OVER (
            PARTITION BY TRUNC(north_america_sale.report_date, 'MONTH')
            ORDER BY SUM(north_america_sale.total_sale) DESC
        ) AS product_category_rank,
        north_america_sale.product_category,
        SUM(north_america_sale.total_sale) AS total_sale
    FROM
        north_america_sale
    GROUP BY
        TRUNC(north_america_sale.report_date, 'MONTH'),
        north_america_sale.product_category
),
performance_window AS (
    SELECT
        product_category_performance.reporting_month,
        product_category_performance.product_category_rank,
        product_category_performance.product_category,
        product_category_performance.total_sale,
        LAG(product_category_performance.product_category_rank) OVER (
            PARTITION BY product_category_performance.product_category
            ORDER BY product_category_performance.reporting_month
        ) AS previous_month_rank,
        LEAD(product_category_performance.product_category_rank) OVER (
            PARTITION BY product_category_performance.product_category
            ORDER BY product_category_performance.reporting_month
        ) AS next_month_rank
    FROM
        product_category_performance
)
SELECT
    performance_window.reporting_month,
    performance_window.product_category,
    performance_window.total_sale
FROM
    performance_window
WHERE
    performance_window.product_category_rank = 1
    AND (
        previous_month_rank = 1
        OR next_month_rank = 1
    )
ORDER BY
    performance_window.reporting_month;

WITH domain_extraction AS (
     SELECT
        oe.customers.cust_email,
        SUBSTR(oe.customers.cust_email,
            INSTR(oe.customers.cust_email,'@', 1, 1) + 1,
            LENGTH(oe.customers.cust_email) 
                - INSTR(oe.customers.cust_email,'@', 1, 1)
        ) AS domain
    FROM
        oe.customers
),
domain_popularity AS (
    SELECT
    	domain_extraction.domain,
        COUNT(*) AS email_count,
        DENSE_RANK() OVER (
        	ORDER BY COUNT(*) DESC
        ) AS domain_ranking
    FROM
    	domain_extraction
    GROUP BY
		domain_extraction.domain
)
SELECT
    domain_popularity.domain,
    domain_popularity.email_count
FROM
    domain_popularity
WHERE
    domain_popularity.domain_ranking <= 2
ORDER BY
	domain_popularity.email_count DESC;

SELECT
	member.member_id AS "Member ID",
	current_member_type.member_type_id AS "Member Type ID",
	current_member_type.description AS "Member Type",
	current_member_type.annual_cost AS "Annual Member Cost",
	current_member_type.discount AS "Member Discount",
	COUNT(purchase.purchase_id) AS "Purchase Count",
	SUM(purchase.purchase_total) AS "Amount Spent",
	ROUND(
		(
			(SUM(purchase.purchase_total)
			/ (1 - current_member_type.discount))
			- SUM(purchase.purchase_total)
		),
		2
	) AS "Amount Saved",
	upgraded_member_type.description AS "Next-tier Member Type",
	upgraded_member_type.annual_cost AS "Next-tier Annual Member Cost",
	upgraded_member_type.discount AS "Next-tier Member Discount",
	ROUND(
		(
			(SUM(purchase.purchase_total)
			/ (1 - current_member_type.discount))
			* upgraded_member_type.discount
		),
		2
	) AS "Next-tier Amount Saved",
	(	
		ROUND(
			(
				(SUM(purchase.purchase_total)
				/ (1 - current_member_type.discount))
				* upgraded_member_type.discount
			),
			2
		) - ROUND(
			(
				(SUM(purchase.purchase_total)
				/ (1 - current_member_type.discount))
				- SUM(purchase.purchase_total)
			),
			2
		)
	) AS "Increased Savings Amount"
FROM
	member
	INNER JOIN
	member_type current_member_type
		ON member.member_type_id = current_member_type.member_type_id
	INNER JOIN
	member_type upgraded_member_type
		ON current_member_type.member_type_id = upgraded_member_type.member_type_id - 1
	INNER JOIN
	purchase
		ON member.member_id = purchase.member_id
GROUP BY
	member.member_id,
	current_member_type.member_type_id,
	current_member_type.description,
	current_member_type.annual_cost,
	current_member_type.discount,
	upgraded_member_type.description,
	upgraded_member_type.annual_cost,
	upgraded_member_type.discount
ORDER BY
	current_member_type.member_type_id;