Untitled workspace

-- Show the customers table
SELECT * FROM customers.csv;

-- Show the subscriptions table
SELECT * FROM subscriptions.csv;

CREATE TABLE Subsidiaries (
	Subsidiary_ID INT, -- New column to be added
    customer_id INT,
    customer_name VARCHAR(50),
    parent_company VARCHAR(50),
	subscription_id INT,
	subscription_type VARCHAR(50)
);

-- Populate the "Subsidiaries" table by combining data from "customers" and "subscriptions" tables
INSERT INTO Subsidiaries (customer_id, customer_name, parent_company, subscription_id, subscription_type)
SELECT c.customer_id, c.customer_name, c.parent_company, s.subscription_id, s.subscription_type
FROM customers.csv c
LEFT JOIN subscriptions.csv s ON c.customer_id = s.customer_id
WHERE parent_company IS NOT NULL;

-- Update the "Subsidiaries" table to set the values for the "Subsidiary_ID" column
WITH numbered_rows AS (
    SELECT customer_id, ROW_NUMBER() OVER(ORDER BY customer_id) AS row_number
    FROM Subsidiaries
)
UPDATE Subsidiaries
SET Subsidiary_ID = numbered_rows.row_number
FROM numbered_rows
WHERE Subsidiaries.customer_id = numbered_rows.customer_id;

SELECT *
FROM Subsidiaries;