<? /* Query: YoY Growth by HSNCode for a Country

SELECT 
    curr.HSNcode,
    curr.year_of_import AS current_year,
    prev.value_of_import AS previous_year_value,
    curr.value_of_import AS current_year_value,
    (curr.value_of_import - prev.value_of_import) AS absolute_growth,
    ROUND(((curr.value_of_import - prev.value_of_import) / prev.value_of_import) * 100, 2) AS percent_growth
FROM main_import_table curr
JOIN main_import_table prev
    ON curr.HSNcode = prev.HSNcode
    AND curr.import_to_country = prev.import_to_country
    AND curr.import_from_country = prev.import_from_country
    AND curr.year_of_import = prev.year_of_import + 1
WHERE curr.import_to_country = 'INDIA'
ORDER BY percent_growth DESC
LIMIT 20;

🔍 USE CASE 2: HSNCodes with Highest Total Imports Over Years

    “What are the top HSN codes imported into India from the world over all years?”

SELECT 
    HSNcode,
    name,
    SUM(value_of_import) AS total_import_value
FROM main_import_table
WHERE import_to_country = 'INDIA'
GROUP BY HSNcode
ORDER BY total_import_value DESC
LIMIT 20;

🔍 USE CASE 3: Top Countries Importing a Particular HSN Code

    “Which countries are importing HSN 850490 the most?”

SELECT 
    import_to_country,
    SUM(value_of_import) AS total_import_value
FROM main_import_table
WHERE HSNcode = 850490
GROUP BY import_to_country
ORDER BY total_import_value DESC
LIMIT 10;

🔍 USE CASE 4: Top Growing Countries for a Particular HSN Code

    “Which countries showed the fastest import value growth for HSN 850490?”

SELECT 
    curr.import_to_country,
    curr.year_of_import,
    curr.value_of_import AS current_value,
    prev.value_of_import AS previous_value,
    (curr.value_of_import - prev.value_of_import) AS absolute_growth,
    ROUND(((curr.value_of_import - prev.value_of_import) / prev.value_of_import) * 100, 2) AS percent_growth
FROM main_import_table curr
JOIN main_import_table prev
    ON curr.import_to_country = prev.import_to_country
    AND curr.HSNcode = prev.HSNcode
    AND curr.import_from_country = prev.import_from_country
    AND curr.year_of_import = prev.year_of_import + 1
WHERE curr.HSNcode = 850490
ORDER BY percent_growth DESC
LIMIT 10;

🔍 USE CASE 5: Total Imports by Country, HSNcode and Year

    “Trends by country, HSN and year – for dashboards or data modeling.”

SELECT 
    import_to_country,
    HSNcode,
    year_of_import,
    SUM(value_of_import) AS total_import_value
FROM main_import_table
GROUP BY import_to_country, HSNcode, year_of_import
ORDER BY import_to_country, HSNcode, year_of_import;


use cases for views
SELECT * FROM `top10_hsn_india_cagr_per_country` WHERE import_to_country='USA';

SELECT * FROM top_growing_hsn_yoy;
SELECT * FROM declining_hsn_codes;
SELECT * FROM top_importing_countries;

*/