CREATE TABLE Brand(
brand_id INTEGER PRIMARY KEY AUTOINCREMENT,
brand_name TEXT NOT NULL UNIQUE,
establishment_year INTEGER,
revenue_in_millions INTEGER CHECK (revenue_in_millions >= 0),
num_of_employees INTEGER CHECK (num_of_employees >= 0),
geographic_region TEXT CHECK (geographic_region IN ('Europe', 'North America', 'Asia', 'Global', 'Australia')) NOT NULL,
positioning TEXT
);
CREATE TABLE Product(
product_id INTEGER PRIMARY KEY AUTOINCREMENT,
product_name TEXT NOT NULL,
category TEXT NOT NULL,
target_functionality TEXT,
strength INTEGER CHECK (strength >= 0),
skin_type TEXT,
retail_price REAL NOT NULL CHECK (retail_price > 0),
max_discount INTEGER CHECK (max_discount > 0 AND max_discount < 100),
brand_id INTEGER, -- Removed NOT NULL constraint
avg_rating REAL CHECK (avg_rating >= 0 AND avg_rating <= 5),
reviews TEXT,
FOREIGN KEY(brand_id) REFERENCES Brand(brand_id)
);
CREATE TABLE Ingredient(
ingredient_id INTEGER PRIMARY KEY AUTOINCREMENT,
ingredient_name TEXT NOT NULL,
target_functionality TEXT, -- anti aging, hydration, etc
strength INTEGER CHECK (strength BETWEEN 1 AND 10), -- on a scale of 1-10
price_per_gram REAL, -- price per gram of the raw ingredient
suited_skin_type TEXT,
is_organic BOOLEAN NOT NULL DEFAULT FALSE,
is_vegan BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE TABLE Formulation(
ingredient_id INTEGER,
product_id INTEGER,
concentration_in_percentage INTEGER CHECK(concentration_in_percentage >= 0 AND concentration_in_percentage <= 100),
PRIMARY KEY (ingredient_id, product_id),
FOREIGN KEY (ingredient_id) REFERENCES Ingredient(ingredient_id),
FOREIGN KEY (product_id) REFERENCES Product(product_id)
);
CREATE TABLE Dermatologist(
derm_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
phone TEXT,
city_of_practice TEXT,
years_experience INTEGER
);
CREATE TABLE Patient(
patient_id INTEGER PRIMARY KEY AUTOINCREMENT,
patient_name TEXT NOT NULL,
city_of_residence TEXT,
skin_condition TEXT,
severity INTEGER, -- on a scale of 1-10?
age INTEGER NOT NULL,
gender TEXT NOT NULL
);
CREATE TABLE ProductRecommendation(
derm_id INTEGER,
product_id INTEGER,
patient_id INTEGER,
date_of_recommendation DATE,
usage_duration_in_months REAL,
usage_instructions TEXT,
PRIMARY KEY (derm_id, product_id, patient_id),
FOREIGN KEY (derm_id) REFERENCES Dermatologist(derm_id),
FOREIGN KEY (product_id) REFERENCES Product(product_id),
FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
);
INSERT INTO Brand (brand_name, establishment_year, revenue_in_millions, num_of_employees, geographic_region, positioning) VALUES
('CeraVe', 2005, 31, 100, 'Global', 'Dermatologist-recommended, affordable, essential ceramides'),
('La Roche-Posay', 1928, 120, 500, 'Global', 'Mineral sunscreens, sensitive skin experts'),
('Augustinus Bader', 2017, 25, 150, 'Global', 'Long-term results, luxury skincare'),
('SkinCeuticals', 1997, 200, 300, 'Global', 'Vitamin C serums and anti-aging specialists'),
('Drunk Elephant', 2013, 845, 200, 'Global', 'Mixable formulas, ceramides, and peptides'),
('Tatcha', 2009, 150, 62766, 'Global', 'Japanese-inspired, oil-free, and natural'),
('Charlotte Tilbury', 2013, 180, 350, 'Global', 'Effortless glow and radiant complexion'),
('Fenty Skin by Rihanna', 2017, 50, 75, 'Global', 'Celebrity-backed, fruit-infused skincare'),
('Dr. Barbara Sturm', 2002, 200, 120, 'Global', 'Luxury, anti-inflammatory, science-based'),
('Mario Badescu', 1967, 100, 250, 'Global', 'Organic fruits and vegetables-based skincare'),
('Dr. Dennis Gross Skincare', 1999, 75, 150, 'Global', 'Dermatologist-founded, clinically formulated'),
('Alpha-H', 1995, 30, 75, 'Australia', 'Vitamin-enriched, hydrating skincare'),
('Peter Thomas Roth', 1993, 150, 200, 'Global', 'Clinical formulations, anti-aging focus'),
('Ole Henriksen', 1975, 80, 180, 'Global', 'Brightening, morning and evening routines'),
('Glossier', 2014, 200, 300, 'Global', 'Skincare essentials, embracing natural beauty'),
('The Ordinary', 2013, 70, 120, 'Global', 'Affordable, ingredient-focused formulations'),
('Farmacy', 2015, 40, 90, 'Global', 'Farm-to-face, natural and sustainable'),
('Versed', 2019, 35, 70, 'Global', 'Affordable, transparent, clean formulas'),
('Alpyn Beauty', 2018, 25, 50, 'Global', 'Natural ingredients, sensitive and dry skin solutions'),
('LANEIGE', 1994, 100, 300, 'Global', 'Trendy, hydrating, K-beauty inspired'),
('Herbivore Botanicals', 2011, 45, 85, 'Global', 'Plant-based, transparent, natural skincare'),
('Cocokind', 2014, 20, 60, 'Global', 'Superfood-focused, sustainable, clean beauty'),
('COSRX', 2015, 50, 120, 'Global', 'Snail mucin-based, hydrating, gentle formulas'),
('Neutrogena', 1930, 2500, 5000, 'Global', 'Affordable, classic, widely accessible'),
('Acure', 2012, 75, 200, 'Global', 'Vegan, natural, and organic personal care'),
('Hero Cosmetics', 2017, 15, 40, 'Global', 'Hydrocolloid acne patches and skincare'),
('Every Man Jack', 2007, 50, 150, 'Global', 'Eco-friendly, affordable, men''s grooming'),
('Aveeno', 1945, 1500, 2500, 'Global', 'Natural ingredients, science-backed formulas'),
('Cetaphil', 1947, 500, 1000, 'Global', 'Simple, universal formulas for sensitive skin'),
('Brickell Men''s Products', 2014, 25, 75, 'Global', 'Natural and organic skincare for men');
INSERT INTO Product (product_name, category, target_functionality, strength, skin_type, retail_price, max_discount, brand_id, avg_rating, reviews) VALUES
('CeraVe Hydrating Facial Cleanser', 'Cleanser', 'Hydration', NULL, 'Normal to Dry Skin', 12.08, NULL, NULL, NULL, NULL),
('La Roche-Posay Cicaplast Baume B5', 'Moisturizer', 'Sensitive Skin', NULL, NULL, 14.04, NULL, NULL, NULL, NULL),
('Augustinus Bader The Rich Cream', 'Moisturizer', 'Hydration', NULL, NULL, 250.00, NULL, NULL, NULL, NULL), -- Mock price
('SkinCeuticals C E Ferulic', 'Serum', 'Anti-Aging', NULL, NULL, 182, NULL, NULL, NULL, NULL),
('Sisley-Paris Nutritive Lip Balm', 'Lip Balm', 'Hydration', NULL, NULL, 95, NULL, NULL, NULL, NULL),
('Caudalie Vinoperfect Dark Spot Serum', 'Serum', 'Dark Spots', NULL, NULL, 82, NULL, NULL, NULL, NULL),
('La Roche-Posay Thermal Spring Water Face Mist', 'Face Mist', 'Hydration', NULL, NULL, 19, NULL, NULL, NULL, NULL),
('EltaMD UV Clear SPF 46', 'Sunscreen', 'Anti-Aging', NULL, NULL, 43, NULL, NULL, NULL, NULL),
('Sunday Riley Good Genes Lactic Acid Treatment', 'Exfoliant', 'Anti-Aging', NULL, NULL, 85, NULL, NULL, NULL, NULL),
('SK-II Facial Treatment Essence', 'Toner', 'Hydration', NULL, NULL, 99, NULL, NULL, NULL, NULL),
('NuFace Mini+ Starter Kit', 'Microcurrent Device', 'Anti-Aging', NULL, NULL, 245, NULL, NULL, NULL, NULL),
('CurrentBody LED Light Therapy Face Mask', 'LED Face Mask', 'Anti-Aging', NULL, NULL, 380, NULL, NULL, NULL, NULL),
('A Perfect World™ SPF 40 Age-Defense Moisturizer with White Tea', 'Moisturizer', 'Anti-Aging', NULL, NULL, 35, NULL, NULL, NULL, NULL),
('Dr. Andrew Weil for Origins™ Mega-Mushroom Relief & Resilience Soothing Treatment Lotion', 'Lotion', 'Hydration', NULL, NULL, 28, NULL, NULL, NULL, NULL),
('Plantscription™ Anti-aging Cleanser', 'Cleanser', 'Anti-Aging', NULL, NULL, 24, NULL, NULL, NULL, NULL),
('GinZing™ Energizing Gel Cream', 'Moisturizer', 'Hydration', NULL, NULL, 30, NULL, NULL, NULL, NULL),
('Checks and Balances™ Frothy Face Wash', 'Cleanser', 'Hydration', NULL, NULL, 21, NULL, NULL, NULL, NULL),
('GinZing™ SPF 40 Energy-Boosting Tinted Moisturizer', 'Moisturizer', 'Hydration', NULL, NULL, 32, NULL, NULL, NULL, NULL),
('Differin Acne Treatment Gel', 'Treatment', 'Acne', NULL, NULL, 14.97, NULL, NULL, 4.7, '2563 reviews'),
('Aveeno Clear Complexion Foaming Facial Cleanser', 'Cleanser', 'Acne', NULL, NULL, 6.77, NULL, NULL, 4.5, '3397 reviews'),
('Neutrogena Hydro Boost Hyaluronic Acid SPF 50 Face Moisturizer Lotion', 'Moisturizer', 'Hydration', NULL, NULL, 19.88, NULL, NULL, 4.4, '390 reviews'),
('Differin Daily Deep Cleanser', 'Cleanser', 'Acne', NULL, NULL, 10.97, NULL, NULL, 4.6, '466 reviews'),
('Olay Vitamin C Peptide 24 Brightening Serum', 'Serum', 'Hydration', NULL, NULL, 22.99, NULL, NULL, 4.6, '1105 reviews'),
('Neutrogena Oil-Free Acne Face Wash Cream', 'Cleanser', 'Acne', NULL, NULL, 6.64, NULL, NULL, 4.5, '399 reviews'),
('Olay Total Effects Face Moisturizer', 'Moisturizer', 'Hydration', NULL, NULL, 17.96, NULL, NULL, 4.1, '3233 reviews'),
('L''Oreal Paris Revitalift Pure Glycolic Acid Serum', 'Serum', 'Anti-Aging', NULL, NULL, 25.97, NULL, NULL, 4.6, '2249 reviews'),
('Bubble Skincare Fresh Start Gel Facial Cleanser', 'Cleanser', 'Hydration', NULL, NULL, 15.98, NULL, NULL, 4.8, '696 reviews'),
('Neutrogena Moisturizing Hydro Boost Hydrating Face Mask', 'Face Mask', 'Hydration', NULL, NULL, 2.77, NULL, NULL, 4.4, '560 reviews'),
('Olay Regenerist Retinol 24 Night Facial Serum', 'Serum', 'Anti-Aging', NULL, NULL, 16.99, NULL, NULL, 4.5, '1658 reviews'),
('Bliss Bright Idea Vitamin C Brightening Face Serum', 'Serum', 'Brightening', NULL, NULL, 24.97, NULL, NULL, 4.7, '678 reviews'),
('ITK Daily Hair Skin Nails Supplement Gummies', 'Supplement', 'Hydration', NULL, NULL, 14.97, NULL, NULL, 4.5, '69 reviews'),
('Neutrogena Rapid Firming Peptide Contour Lift Face Cream', 'Moisturizer', 'Anti-Aging', NULL, NULL, 34.94, NULL, NULL, 4.4, '294 reviews'),
('Mighty Patch Invisible from Hero Cosmetics', 'Treatment', 'Acne', NULL, NULL, 10.98, NULL, NULL, 4.8, '835 reviews'),
('Olay Day Eye Cream', 'Eye Cream', 'Hydration', NULL, NULL, 29.98, NULL, NULL, 4.6, '895 reviews'),
('Neutrogena Age Shield Face Oil-Free Sunscreen SPF 70', 'Sunscreen', 'Hydration', NULL, NULL, 9.58, NULL, NULL, 4.5, '768 reviews'),
('Versed Guards Up Daily Mineral Sunscreen Broad Spectrum SPF 35', 'Sunscreen', 'Hydration', NULL, NULL, 19.97, NULL, NULL, 4.2, '408 reviews'),
('Versed Brand Skin Care, Stroke of Brilliance Brightening Serum', 'Serum', 'Brightening', NULL, NULL, 19.97, NULL, NULL, 4.5, '264 reviews'),
('Thayers Alcohol-Free Rose Petal Witch Hazel Facial Toner', 'Toner', 'Hydration', NULL, NULL, 7.97, NULL, NULL, 4.6, '1966 reviews'),
('Versed Wash It Out Gel Face Cleanser', 'Cleanser', 'Hydration', NULL, NULL, 9.97, NULL, NULL, 4.5, '297 reviews'),
('Bubble Skincare Day Dream Serum', 'Serum', 'Brightening', NULL, NULL, 14.98, NULL, NULL, NULL, NULL),
('Clean & Clear Morning Burst Oil-Free Hydrating Face Wash', 'Cleanser', 'Hydration', NULL, NULL, 5.97, NULL, NULL, 4.6, NULL),
('Olay Age Defying Anti-Wrinkle Day Face Lotion with Sunscreen SPF 15', 'Moisturizer', 'Anti-Aging', NULL, NULL, 14.98, NULL, NULL, NULL, NULL),
('Cetaphil Healthy Radiance Whipped Day Cream with SPF 30', 'Moisturizer', 'Hydration', NULL, NULL, 15.72, NULL, NULL, NULL, NULL),
('No7 Lift & Luminate Triple Action Day Cream SPF 30 Moisturizer', 'Moisturizer', 'Hydration', NULL, NULL, 20.99, NULL, NULL, NULL, NULL),
('Olay Age Defying 2 in 1 Day Cream Plus Serum', 'Moisturizer', 'Anti-Wrinkle', NULL, NULL, 14.98, NULL, NULL, NULL, NULL),
('Olay Regenerist Ultra Rich Face Moisturizer', 'Moisturizer', 'Hydration', NULL, NULL, 16, NULL, NULL, NULL, NULL),
('ITK Deep Moisture Rich Face Cream', 'Moisturizer', 'Hydration', NULL, NULL, 14.97, NULL, NULL, NULL, NULL),
('Cicalfate Lips Restorative Lip Cream by Avène Skin Care', 'Lip Balm', 'Hydration', NULL, NULL, 20, NULL, NULL, NULL, NULL),
('Cicalfate Restorative Protective Cream by Avène Skin Care', 'Moisturizer', 'Hydration', NULL, NULL, 40, NULL, NULL, NULL, NULL),
('Brightening UV Defense Sunscreen SPF 50 by SkinCeuticals', 'Sunscreen', 'Brightening', NULL, NULL, 50, NULL, NULL, NULL, NULL),
('Dermal Repair Cream by SkinMedica', 'Moisturizer', 'Hydration', NULL, NULL, 120, NULL, NULL, NULL, NULL),
('Even Brightening Advanced Treatment by SkinMedica', 'Treatment', 'Brightening', NULL, NULL, 80, NULL, NULL, NULL, NULL);
INSERT INTO Ingredient (ingredient_name, target_functionality, strength, price_per_gram, suited_skin_type, is_organic, is_vegan)
VALUES
('Alpha-Hydroxy Acid (AHA)', 'Exfoliation, anti-aging', 5, 0.2, 'All skin types', 0, 1),
('Beta-Hydroxy Acid (Salicylic Acid)', 'Exfoliation, anti-aging', 4, 0.15, 'All skin types', 0, 1),
('Retinol', 'anti-aging', 7, 0.5, 'All skin types', 0, 1),
('Vitamin C', 'anti-aging, brightening', 8, 0.8, 'All skin types', 0, 1),
('Vitamin E', 'anti-aging, soothing', 6, 0.3, 'All skin types', 0, 1),
('Hyaluronic Acid', 'Hydration', 9, 0.4, 'Dry and mature skin', 0, 1),
('Niacinamide', 'Hydration, soothing', 7, 0.25, 'All skin types', 0, 1),
('Aloe Vera', 'Soothing', 4, 0.1, 'All skin types', 1, 1),
('Jojoba Oil', 'Hydration', 5, 0.6, 'All skin types', 0, 1),
('Rosehip Oil', 'Hydration, brightening', 6, 0.7, 'All skin types', 0, 1),
('Glycerin', 'Hydration', 7, 0.2, 'All skin types', 0, 1),
('Peptides', 'Anti-aging', 6, 0.9, 'All skin types', 0, 1),
('Ceramides', 'Hydration', 5, 0.15, 'Dry skin', 0, 1),
('Azelaic Acid', 'Exfoliation, soothing', 3, 0.1, 'All skin types', 0, 1),
('Bakuchiol', 'Anti-aging', 4, 0.3, 'Sensitive skin', 0, 1),
('Benzoyl Peroxide', 'Acne treatment', 2, 0.05, 'Oily skin', 0, 1),
('Caffeine', 'Reducing dark circles and puffiness', 3, 0.2, 'All skin types', 0, 1),
('Squalane', 'Hydration, anti-aging', 5, 0.4, 'All skin types', 0, 1),
('Castor Oil', 'Hydration', 4, 0.2, 'Dry and mature skin', 0, 1),
('Polyglutamic Acid', 'Hydration', 6, 0.3, 'All skin types', 0, 1),
('Argan Oil', 'Hydration', 5, 0.5, 'All skin types', 0, 1),
('Lactic Acid', 'Exfoliation', 4, 0.1, 'All skin types', 0, 1),
('Glycolic Acid', 'Exfoliation', 5, 0.12, 'Acne-prone skin', 0, 1),
('Licorice Root Extract', 'Brightening, soothing', 4, 0.2, 'All skin types', 0, 1),
('Green Tea', 'Anti-aging, brightening', 6, 0.3, 'All skin types', 0, 1),
('Hemp Seed Oil', 'Hydration, anti-inflammatory', 5, 0.4, 'All skin types', 0, 1),
('Evening Primrose Oil', 'Hydration, soothing', 4, 0.2, 'Dry and rough skin', 0, 1),
('Cedarwood Oil', 'Fragrance, acne treatment', 3, 0.1, 'All skin types', 0, 1),
('Lavender Oil', 'Fragrance, soothing', 4, 0.15, 'All skin types', 0, 1),
('Olive Oil', 'Hydration', 5, 0.1, 'Sensitive skin', 0, 1),
('Pomegranate Extract', 'Anti-aging', 6, 0.6, 'All skin types', 0, 1),
('Red Raspberry Seed Oil', 'Hydration, sun protection', 5, 0.5, 'All skin types', 0, 1);
INSERT INTO Formulation (ingredient_id, product_id, concentration_in_percentage)
VALUES
(1, 1, 5),
(2, 1, 2),
(3, 2, 7),
(4, 2, 10),
(5, 3, 3),
(6, 3, 5),
(7, 4, 8),
(8, 4, 2),
(9, 5, 9),
(10, 5, 6),
(11, 6, 4),
(12, 6, 3),
(13, 7, 5),
(14, 7, 2),
(15, 8, 7),
(16, 8, 3),
(17, 9, 6),
(18, 9, 9),
(19, 10, 5),
(20, 10, 8),
(21, 11, 7),
(22, 11, 4),
(23, 12, 3),
(24, 12, 6),
(25, 13, 5),
(26, 13, 10),
(27, 14, 4),
(28, 14, 7),
(29, 15, 3),
(30, 15, 5),
(31, 16, 6),
(32, 16, 9),
(33, 17, 4),
(34, 17, 7),
(35, 18, 5),
(36, 18, 8),
(37, 19, 6),
(38, 19, 3),
(39, 20, 5),
(40, 20, 10),
(41, 21, 7),
(42, 21, 2),
(43, 22, 6),
(44, 22, 9),
(45, 23, 3),
(46, 23, 5),
(47, 24, 4),
(48, 24, 7),
(49, 25, 6),
(50, 25, 9),
(51, 26, 5),
(52, 26, 8),
(53, 27, 4),
(54, 27, 6),
(55, 28, 3),
(56, 28, 5),
(57, 29, 2),
(58, 29, 4),
(59, 30, 7),
(60, 30, 3),
(61, 31, 6),
(62, 31, 9),
(63, 32, 5),
(64, 32, 8),
(65, 33, 4),
(66, 33, 7),
(67, 34, 3),
(68, 34, 6),
(69, 35, 5),
(70, 35, 10),
(71, 36, 4),
(72, 36, 7),
(73, 37, 3),
(74, 37, 6),
(75, 38, 5),
(76, 38, 8),
(77, 39, 4),
(78, 39, 9),
(79, 40, 3),
(80, 40, 6),
(81, 41, 5),
(82, 41, 10),
(83, 42, 4),
(84, 42, 7),
(85, 43, 3),
(86, 43, 6),
(87, 44, 5),
(88, 44, 8),
(89, 45, 4),
(90, 45, 7),
(91, 46, 3),
(92, 46, 6),
(93, 47, 5),
(94, 47, 9),
(95, 48, 4),
(96, 48, 7),
(97, 49, 3),
(98, 49, 6),
(99, 50, 5),
(100, 50, 8);
INSERT INTO Dermatologist (name, email, phone, city_of_practice, years_experience)
VALUES
('Dr. Emma Johnson', 'ejohnson@derm.com', '555-123-4567', 'New York City', 15),
('Dr. Michael Brown', 'mbrown@dermclinic.net', '555-987-6543', 'Los Angeles', 10),
('Dr. Olivia Wilson', 'owilson@skinhealth.org', '555-555-5555', 'Chicago', 7),
('Dr. Benjamin Davis', 'bdavis@dermatology.com', '555-111-2222', 'Houston', 20),
('Dr. Sophia Martinez', 'smartinez@skinandhealth.com', '555-666-7777', 'Miami', 12),
('Dr. Ethan Miller', 'emiller@dermcenter.net', '555-888-9999', 'Seattle', 5),
('Dr. Lily Chen', 'lchen@skinandwellness.com', '555-000-1111', 'San Francisco', 3),
('Dr. Amelia Carter', 'acarter@dermsolutions.org', '555-444-5555', 'Atlanta', 8),
('Dr. Gabriel Sanchez', 'gsanchez@skinsolutions.com', '555-222-3333', 'Boston', 15),
('Dr. Isabella Hernandez', 'ihernandez@dermclinic.com', '555-777-8888', 'Dallas', 10),
('Dr. William Nguyen', 'wnguyen@skinhealthcenter.net', '555-999-0000', 'Philadelphia', 7),
('Dr. Charlotte Lee', 'clee@dermassociates.com', '555-333-6666', 'San Diego', 13),
('Dr. Daniel Wilson', 'dwilson@skinsolutions.net', '555-444-7777', 'Miami', 9),
('Dr. Victoria Miller', 'vmiller@dermclinic.org', '555-555-8888', 'Denver', 6),
('Dr. Sebastian Brown', 'sbrown@skinhealthcenter.com', '555-666-9999', 'Chicago', 11),
('Dr. Eleanor Martinez', 'emartinez@dermatologygroup.net', '555-777-0000', 'Houston', 17),
('Dr. Alexander Chen', 'achen@skinsolutionsgroup.com', '555-888-1111', 'Seattle', 4),
('Dr. Abigail Carter', 'acarter@dermcenter.org', '555-999-2222', 'Boston', 8),
('Dr. Samuel Sanchez', 'ssanchez@dermassociates.net', '555-000-3333', 'Los Angeles', 14),
('Dr. Sophia Davis', 'sdavis@skinhealth.com', '555-111-4444', 'Atlanta', 10),
('Dr. William Anderson', 'wanderson@dermatology.org', '555-222-5555', 'Dallas', 7),
('Dr. Amelia Nguyen', 'anguyen@skinsolutions.net', '555-333-6666', 'San Francisco', 5),
('Dr. Benjamin Lee', 'blee@dermclinic.com', '555-444-7777', 'Philadelphia', 12),
('Dr. Charlotte Wilson', 'cwilson@skinhealthcenter.org', '555-555-8888', 'Phoenix', 9),
('Dr. Daniel Miller', 'dmiller@dermassociates.net', '555-666-9999', 'Houston', 6),
('Dr. Victoria Brown', 'vbrown@skinhealth.com', '555-777-0000', 'San Diego', 11),
('Dr. Sebastian Martinez', 'smartinez@skinsolutionsgroup.com', '555-888-1111', 'Miami', 10),
('Dr. Eleanor Chen', 'echen@dermatologygroup.net', '555-999-2222', 'Denver', 15),
('Dr. Alexander Carter', 'acarter@skinsolutions.org', '555-000-3333', 'Chicago', 7),
('Dr. Abigail Sanchez', 'asanchez@dermcenter.net', '555-111-4444', 'Seattle', 12),
('Dr. Samuel Davis', 'sdavis@dermassociates.org', '555-222-5555', 'Boston', 8),
('Dr. William Thompson', 'wthompson@skinhealthcenter.com', '555-333-6666', 'Los Angeles', 13),
('Dr. Charlotte Anderson', 'canderson@skinsolutions.net', '555-444-7777', 'Atlanta', 9),
('Dr. Daniel Nguyen', 'dnguyen@skinhealth.org', '555-555-8888', 'Dallas', 6),
('Dr. Victoria Lee', 'vlee@dermclinic.com', '555-666-9999', 'San Francisco', 11),
('Dr. Benjamin Wilson', 'bwilson@skinhealthcenter.org', '555-777-0000', 'Houston', 10),
('Dr. Charlotte Miller', 'cmiller@dermatologygroup.net', '555-888-1111', 'Miami', 7),
('Dr. Daniel Brown', 'dbrown@skinsolutionsgroup.com', '555-999-2222', 'Denver', 12),
('Dr. Victoria Martinez', 'vmartinez@dermcenter.net', '555-000-3333', 'Chicago', 8),
('Dr. Sebastian Chen', 'schen@dermassociates.org', '555-111-4444', 'Seattle', 13),
('Dr. Eleanor Carter', 'ecarter@skinhealthcenter.com', '555-222-5555', 'Boston', 9),
('Dr. Alexander Sanchez', 'asanchez@skinsolutions.net', '555-333-6666', 'Los Angeles', 10),
('Dr. Abigail Davis', 'adavis@skinhealth.org', '555-444-7777', 'Atlanta', 6),
('Dr. Samuel Miller', 'smiller@dermclinic.com', '555-555-8888', 'Dallas', 11),
('Dr. William Lee', 'wlee@dermatologygroup.net', '555-666-9999', 'San Francisco', 14),
('Dr. Charlotte Wilson', 'cwilson@skinsolutionsgroup.com', '555-777-0000', 'Houston', 12),
('Dr. Daniel Nguyen', 'dnguyen@dermcenter.net', '555-888-1111', 'Miami', 7),
('Dr. Victoria Thompson', 'vthompson@skinhealth.com', '555-999-2222', 'Denver', 13),
('Dr. Benjamin Brown', 'bbrown@skinsolutionsgroup.com', '555-000-3333', 'Chicago', 8),
('Dr. Sebastian Martinez', 'smartinez@dermassociates.org', '555-111-4444', 'Seattle', 9),
('Dr. Eleanor Chen', 'echen@skinhealthcenter.com', '555-222-5555', 'Boston', 14),
('Dr. Alexander Carter', 'acarter@skinsolutions.net', '555-333-6666', 'Los Angeles', 10),
('Dr. Abigail Sanchez', 'asanchez@skinhealth.org', '555-444-7777', 'Atlanta', 11),
('Dr. Samuel Davis', 'sdavis@dermclinic.com', '555-555-8888', 'Dallas', 12),
('Dr. William Miller', 'wmiller@dermatologygroup.net', '555-666-9999', 'San Francisco', 9),
('Dr. Charlotte Lee', 'clee@skinsolutionsgroup.com', '555-777-0000', 'Houston', 15),
('Dr. Daniel Wilson', 'dwilson@dermcenter.net', '555-888-1111', 'Miami', 13),
('Dr. Victoria Nguyen', 'vnguyen@skinhealth.com', '555-999-2222', 'Denver', 10),
('Dr. Benjamin Thompson', 'bthompson@skinsolutionsgroup.com', '555-000-3333', 'Chicago', 11),
('Dr. Charlotte Brown', 'cbrown@dermassociates.org', '555-111-4444', 'Seattle', 12),
('Dr. Daniel Martinez', 'dmartinez@skinhealthcenter.net', '555-222-5555', 'Boston', 8),
('Dr. Victoria Chen', 'vchen@skinsolutions.net', '555-333-6666', 'Los Angeles', 9),
('Dr. Sebastian Carter', 'scarter@skinhealth.org', '555-444-7777', 'Atlanta', 14),
('Dr. Eleanor Sanchez', 'esanchez@dermclinic.com', '555-555-8888', 'Dallas', 10),
('Dr. Alexander Davis', 'adavis@dermatologygroup.net', '555-666-9999', 'San Francisco', 11),
('Dr. Abigail Miller', 'amiller@skinsolutionsgroup.com', '555-777-0000', 'Houston', 12),
('Dr. Samuel Lee', 'slee@dermcenter.net', '555-888-1111', 'Miami', 13),
('Dr. William Wilson', 'wwilson@skinhealth.com', '555-999-2222', 'Denver', 14),
('Dr. Charlotte Nguyen', 'cnguyen@skinsolutionsgroup.com', '555-000-3333', 'Chicago', 9),
('Dr. Daniel Thompson', 'dthompson@dermassociates.org', '555-111-4444', 'Seattle', 10),
('Dr. Victoria Brown', 'vbrown@skinhealthcenter.net', '555-222-5555', 'Boston', 11),
('Dr. Benjamin Martinez', 'bmartinez@skinsolutions.net', '555-333-6666', 'Los Angeles', 12),
('Dr. Charlotte Chen', 'cchen@skinhealth.org', '555-444-7777', 'Atlanta', 13),
('Dr. Daniel Carter', 'dcarter@dermclinic.com', '555-555-8888', 'Dallas', 14),
('Dr. Victoria Sanchez', 'vsanchez@dermatologygroup.net', '555-666-9999', 'San Francisco', 10),
('Dr. Sebastian Miller', 'smiller@skinsolutionsgroup.com', '555-777-0000', 'Houston', 11),
('Dr. Eleanor Lee', 'elee@dermcenter.net', '555-888-1111', 'Miami', 12),
('Dr. Alexander Wilson', 'awilson@skinhealth.com', '555-999-2222', 'Denver', 9),
('Dr. Abigail Nguyen', 'anguyen@skinsolutionsgroup.com', '555-000-3333', 'Chicago', 13);
INSERT INTO Patient (patient_name, city_of_residence, skin_condition, severity, age, gender)
VALUES
('Ava Williams', 'New York City', 'Acne', 6, 23, 'Female'),
('Oliver Taylor', 'Los Angeles', 'Eczema', 4, 26, 'Male'),
('Sophia Moore', 'Chicago', 'Psoriasis', 7, 30, 'Female'),
('Ethan Anderson', 'Houston', 'Dry Skin', 5, 19, 'Male'),
('Isabella Jackson', 'Miami', 'Oily Skin', 8, 27, 'Female'),
('William Miller', 'Seattle', 'Rosacea', 2, 32, 'Male'),
('Emma Wilson', 'San Francisco', 'Aging Skin', 6, 45, 'Female'),
('Benjamin Carter', 'Atlanta', 'Sun Damage', 4, 55, 'Male'),
('Olivia Martinez', 'Boston', 'Sensitive Skin', 3, 21, 'Female'),
('Noah Johnson', 'Dallas', 'Dandruff', 5, 29, 'Male'),
('Sophia Anderson', 'Denver', 'Acne', 7, 24, 'Female'),
('Ethan Thompson', 'Phoenix', 'Eczema', 6, 31, 'Male'),
('Isabella Wilson', 'Philadelphia', 'Psoriasis', 5, 20, 'Female'),
('William Davis', 'San Diego', 'Dry Skin', 3, 18, 'Male'),
('Emma Miller', 'Detroit', 'Oily Skin', 7, 26, 'Female'),
('Benjamin Brown', 'San Jose', 'Rosacea', 4, 34, 'Male'),
('Ava Lee', 'Indianapolis', 'Aging Skin', 5, 42, 'Female'),
('Oliver Park', 'Jacksonville', 'Sun Damage', 3, 52, 'Male'),
('Sophia Johnson', 'San Francisco', 'Sensitive Skin', 2, 25, 'Female'),
('Ethan Smith', 'Memphis', 'Dandruff', 6, 27, 'Male'),
('Isabella Taylor', 'Washington', 'Acne', 5, 21, 'Female'),
('William Moore', 'Nashville', 'Eczema', 4, 19, 'Male'),
('Emma Anderson', 'Las Vegas', 'Psoriasis', 6, 28, 'Female'),
('Benjamin Wilson', 'Louisville', 'Dry Skin', 5, 30, 'Male'),
('Ava Davis', 'Portland', 'Oily Skin', 7, 24, 'Female'),
('Oliver Miller', 'Oklahoma City', 'Rosacea', 3, 36, 'Male'),
('Sophia Chen', 'Milwaukee', 'Aging Skin', 6, 40, 'Female'),
('Ethan Martinez', 'Albuquerque', 'Sun Damage', 4, 51, 'Male'),
('Isabella Carter', 'Tucson', 'Sensitive Skin', 2, 22, 'Female'),
('William Johnson', 'Fresno', 'Dandruff', 5, 26, 'Male'),
('Emma Hernandez', 'Sacramento', 'Acne', 7, 20, 'Female'),
('Benjamin Thompson', 'Long Beach', 'Eczema', 6, 18, 'Male'),
('Ava Wilson', 'Kansas City', 'Psoriasis', 5, 29, 'Female'),
('Oliver Davis', 'Mesa', 'Dry Skin', 4, 31, 'Male'),
('Sophia Miller', 'Virginia Beach', 'Oily Skin', 6, 25, 'Female'),
('Ethan Brown', 'Omaha', 'Rosacea', 3, 37, 'Male'),
('Isabella Lee', 'Oakland', 'Aging Skin', 5, 41, 'Female'),
('William Park', 'Minneapolis', 'Sun Damage', 4, 53, 'Male'),
('Emma Martinez', 'Tulsa', 'Sensitive Skin', 3, 23, 'Female'),
('Benjamin Sanchez', 'Cleveland', 'Dandruff', 6, 28, 'Male'),
('Ava Johnson', 'Wichita', 'Acne', 5, 21, 'Female'),
('Oliver Hernandez', 'New Orleans', 'Eczema', 4, 19, 'Male'),
('Sophia Thompson', 'Bakersfield', 'Psoriasis', 6, 27, 'Female'),
('Ethan Johnson', 'Tampa', 'Dry Skin', 5, 32, 'Male'),
('Isabella Smith', 'Honolulu', 'Oily Skin', 7, 26, 'Female'),
('William Anderson', 'Aurora', 'Rosacea', 4, 35, 'Male'),
('Emma Thompson', 'Anaheim', 'Aging Skin', 6, 40, 'Female'),
('Benjamin Wilson', 'Santa Ana', 'Sun Damage', 5, 47, 'Male'),
('Ava Carter', 'Lexington', 'Sensitive Skin', 3, 22, 'Female'),
('Oliver Sanchez', 'Stockton', 'Dandruff', 2, 24, 'Male'),
('Sophia Hernandez', 'Cincinnati', 'Acne', 6, 20, 'Female'),
('Ethan Johnson', 'Pittsburgh', 'Eczema', 5, 18, 'Male'),
('Isabella Miller', 'Toledo', 'Psoriasis', 4, 29, 'Female'),
('William Brown', 'St. Louis', 'Dry Skin', 3, 31, 'Male'),
('Emma Davis', 'Riverside', 'Oily Skin', 5, 25, 'Female'),
('Benjamin Anderson', 'Raleigh', 'Rosacea', 6, 33, 'Male'),
('Ava Wilson', 'Orlando', 'Aging Skin', 4, 42, 'Female'),
('Oliver Carter', 'St. Petersburg', 'Sun Damage', 5, 50, 'Male'),
('Sophia Martinez', 'Jersey City', 'Sensitive Skin', 3, 21, 'Female'),
('Ethan Sanchez', 'Norfolk', 'Dandruff', 7, 27, 'Male'),
('Isabella Brown', 'Lexington', 'Acne', 6, 23, 'Female'),
('William Thompson', 'Durham', 'Eczema', 5, 19, 'Male'),
('Emma Johnson', 'Henderson', 'Psoriasis', 4, 28, 'Female'),
('Benjamin Miller', 'Chula Vista', 'Dry Skin', 3, 30, 'Male'),
('Ava Davis', 'Irvine', 'Oily Skin', 5, 24, 'Female'),
('Oliver Anderson', 'Laredo', 'Rosacea', 6, 36, 'Male'),
('Sophia Wilson', 'Madison', 'Aging Skin', 4, 41, 'Female'),
('Ethan Carter', 'Lubbock', 'Sun Damage', 5, 51, 'Male'),
('Isabella Martinez', 'Chandler', 'Sensitive Skin', 6, 22, 'Female'),
('William Sanchez', 'Scottsdale', 'Dandruff', 7, 26, 'Male'),
('Emma Brown', 'North Las Vegas', 'Acne', 5, 20, 'Female'),
('Benjamin Hernandez', 'Richmond', 'Eczema', 4, 18, 'Male');
INSERT INTO ProductRecommendation (derm_id, product_id, patient_id, date_of_recommendation, usage_duration_in_months, usage_instructions)
VALUES
(1, 1, 1, '2023-08-06', 3, 'Use twice daily, morning and night.'),
(2, 2, 2, '2023-07-15', 2, 'Apply a thin layer to affected areas twice daily.'),
(3, 3, 3, '2023-09-02', 4, 'Massage into skin every night before bed.'),
(4, 4, 4, '2023-08-20', 6, 'Apply 4-5 drops to face, neck, and chest morning and night.'),
(5, 5, 5, '2023-06-10', 5, 'Use twice daily after cleansing.'),
(6, 6, 6, '2023-08-14', 3, 'Spray onto face as needed for instant hydration.'),
(7, 7, 7, '2023-05-25', 4, 'Apply liberally 15 minutes before sun exposure.'),
(8, 8, 8, '2023-07-08', 2, 'Use 2-3 times per week at night.'),
(9, 9, 9, '2023-09-01', 5, 'Use daily, morning and night.'),
(10, 10, 10, '2023-08-25', 4, 'Use 3-4 times per week for 10 minutes.'),
(11, 11, 11, '2023-07-12', 3, 'Apply a thin layer to face and neck daily.'),
(12, 12, 12, '2023-06-20', 2, 'Use twice daily after cleansing.'),
(13, 13, 13, '2023-08-05', 5, 'Apply a small amount to face and neck daily.'),
(14, 14, 14, '2023-09-10', 4, 'Use twice daily, morning and night.'),
(15, 15, 15, '2023-07-25', 3, 'Apply a thin layer to face and neck daily.'),
(16, 16, 16, '2023-08-18', 2, 'Use twice daily, morning and night.'),
(17, 17, 17, '2023-06-05', 6, 'Apply liberally 15 minutes before sun exposure.'),
(18, 18, 18, '2023-07-30', 4, 'Use twice daily, morning and night.'),
(19, 19, 19, '2023-08-15', 5, 'Apply a thin layer to face and neck at night.'),
(20, 20, 20, '2023-09-03', 3, 'Use twice daily after cleansing.'),
(21, 21, 21, '2023-07-20', 2, 'Apply a thin layer to affected areas twice daily.'),
(22, 22, 22, '2023-08-08', 4, 'Use twice daily, morning and night.'),
(23, 23, 23, '2023-06-15', 5, 'Apply liberally 15 minutes before sun exposure.'),
(24, 24, 24, '2023-07-31', 3, 'Use 2-3 times per week at night.'),
(25, 25, 25, '2023-08-10', 6, 'Use daily, morning and night.'),
(26, 26, 26, '2023-09-05', 4, 'Use 3-4 times per week for 10 minutes.'),
(27, 27, 27, '2023-07-20', 5, 'Apply a small amount to face and neck daily.'),
(28, 28, 28, '2023-08-03', 3, 'Use twice daily after cleansing.'),
(29, 29, 29, '2023-06-18', 2, 'Apply a thin layer to face and neck daily.'),
(30, 30, 30, '2023-07-05', 4, 'Use twice daily, morning and night.'),
(31, 31, 31, '2023-08-20', 5, 'Apply liberally 15 minutes before sun exposure.'),
(32, 32, 32, '2023-09-15', 3, 'Use 2-3 times per week at night.'),
(33, 33, 33, '2023-07-30', 6, 'Use daily, morning and night.'),
(34, 34, 34, '2023-08-12', 4, 'Apply a thin layer to affected areas twice daily.'),
(35, 35, 35, '2023-06-25', 5, 'Use twice daily after cleansing.'),
(36, 36, 36, '2023-07-10', 3, 'Apply liberally 15 minutes before sun exposure.'),
(37, 37, 37, '2023-08-25', 2, 'Use 3-4 times per week for 10 minutes.'),
(38, 38, 38, '2023-09-08', 4, 'Apply a small amount to face and neck daily.'),
(39, 39, 39, '2023-07-20', 3, 'Use twice daily, morning and night.'),
(40, 40, 40, '2023-08-05', 5, 'Apply a thin layer to face and neck at night.'),
(41, 41, 41, '2023-06-15', 6, 'Apply a thin layer to face and neck daily.'),
(42, 42, 42, '2023-07-31', 4, 'Use twice daily after cleansing.'),
(43, 43, 43, '2023-08-15', 3, 'Apply liberally 15 minutes before sun exposure.'),
(44, 44, 44, '2023-09-10', 2, 'Use 2-3 times per week at night.'),
(45, 45, 45, '2023-07-25', 4, 'Use daily, morning and night.'),
(46, 46, 46, '2023-08-10', 5, 'Apply a small amount to face and neck daily.'),
(47, 47, 47, '2023-06-20', 3, 'Use twice daily, morning and night.'),
(48, 48, 48, '2023-07-05', 2, 'Apply a thin layer to affected areas twice daily.'),
(49, 49, 49, '2023-08-20', 4, 'Apply liberally 15 minutes before sun exposure.'),
(50, 50, 50, '2023-09-03', 6, 'Use 3-4 times per week for 10 minutes.'),
(51, 51, 51, '2023-07-15', 5, 'Apply a small amount to face and neck daily.'),
(52, 52, 52, '2023-08-30', 3, 'Use twice daily after cleansing.'),
(53, 53, 53, '2023-06-10', 4, 'Apply a thin layer to face and neck at night.'),
(54, 54, 54, '2023-07-25', 2, 'Use twice daily, morning and night.'),
(55, 55, 55, '2023-08-10', 3, 'Apply liberally 15 minutes before sun exposure.'),
(56, 56, 56, '2023-09-05', 5, 'Use 2-3 times per week at night.'),
(57, 57, 57, '2023-07-20', 4, 'Use daily, morning and night.'),
(58, 58, 58, '2023-08-04', 6, 'Apply a small amount to face and neck daily.'),
(59, 59, 59, '2023-06-15', 5, 'Use twice daily after cleansing.'),
(60, 60, 60, '2023-07-31', 3, 'Apply a thin layer to affected areas twice daily.'),
(61, 61, 61, '2023-08-15', 2, 'Apply liberally 15 minutes before sun exposure.'),
(62, 62, 62, '2023-09-10', 4, 'Use 3-4 times per week for 10 minutes.'),
(63, 63, 63, '2023-07-25', 3, 'Use daily, morning and night.'),
(64, 64, 64, '2023-08-10', 5, 'Apply a small amount to face and neck daily.'),
(65, 65, 65, '2023-06-20', 4, 'Use twice daily, morning and night.'),
(66, 66, 66, '2023-07-05', 6, 'Apply a thin layer to face and neck at night.'),
(67, 67, 67, '2023-08-20', 3, 'Apply liberally 15 minutes before sun exposure.'),
(68, 68, 68, '2023-09-03', 2, 'Use 2-3 times per week at night.'),
(69, 69, 69, '2023-07-15', 4, 'Use daily, morning and night.'),
(70, 70, 70, '2023-08-10', 5, 'Apply a small amount to face and neck daily.'),
(71, 71, 71, '2023-06-25', 3, 'Use twice daily after cleansing.'),
(72, 72, 72, '2023-07-10', 2, 'Apply a thin layer to affected areas twice daily.'),
(73, 73, 73, '2023-08-25', 4, 'Apply liberally 15 minutes before sun exposure.'),
(74, 74, 74, '2023-09-15', 6, 'Use 3-4 times per week for 10 minutes.'),
(75, 75, 75, '2023-07-31', 5, 'Use daily, morning and night.'),
(76, 76, 76, '2023-08-15', 4, 'Apply a small amount to face and neck daily.'),
(77, 77, 77, '2023-06-10', 3, 'Use twice daily, morning and night.'),
(78, 78, 78, '2023-07-25', 2, 'Apply a thin layer to face and neck at night.'),
(79, 79, 79, '2023-08-10', 4, 'Apply liberally 15 minutes before sun exposure.'),
(80, 80, 80, '2023-09-05', 3, 'Use 2-3 times per week at night.'),
(81, 81, 81, '2023-07-20', 5, 'Use daily, morning and night.'),
(82, 82, 82, '2023-08-04', 6, 'Apply a small amount to face and neck daily.'),
(83, 83, 83, '2023-06-15', 4, 'Use twice daily after cleansing.'),
(84, 84, 84, '2023-07-31', 5, 'Apply a thin layer to affected areas twice daily.'),
(85, 85, 85, '2023-08-15', 3, 'Apply liberally 15 minutes before sun exposure.'),
(86, 86, 86, '2023-09-10', 2, 'Use 3-4 times per week for 10 minutes.'),
(87, 87, 87, '2023-07-25', 4, 'Use daily, morning and night.'),
(88, 88, 88, '2023-08-10', 3, 'Apply a small amount to face and neck daily.'),
(89, 89, 89, '2023-06-20', 5, 'Use twice daily, morning and night.'),
(90, 90, 90, '2023-07-05', 6, 'Apply a thin layer to face and neck at night.'),
(91, 91, 91, '2023-08-20', 4, 'Apply liberally 15 minutes before sun exposure.'),
(92, 92, 92, '2023-09-03', 3, 'Use 2-3 times per week at night.'),
(93, 93, 93, '2023-07-15', 2, 'Use daily, morning and night.'),
(94, 94, 94, '2023-08-10', 4, 'Apply a small amount to face and neck daily.'),
(95, 95, 95, '2023-06-25', 5, 'Use twice daily after cleansing.'),
(96, 96, 96, '2023-07-10', 3, 'Apply a thin layer to affected areas twice daily.'),
(97, 97, 97, '2023-08-25', 6, 'Apply liberally 15 minutes before sun exposure.'),
(98, 98, 98, '2023-09-15', 4, 'Use 3-4 times per week for 10 minutes.'),
(99, 99, 99, '2023-07-31', 5, 'Use daily, morning and night.'),
(100, 100, 100, '2023-08-15', 3, 'Apply a small amount to face and neck daily.');
-- 51+ recs
-- WITH ProductRecommendations AS (
-- SELECT
-- p.product_name,
-- p.avg_rating,
-- pat.age,
-- pr.usage_duration_in_months,
-- pat.severity,
-- COUNT(*) AS recommendation_count
-- FROM ProductRecommendation pr
-- JOIN Patient pat ON pr.patient_id = pat.patient_id
-- JOIN Product p ON pr.product_id = p.product_id
-- GROUP BY p.product_id, pat.age
-- ),
-- AgeGroupStats AS (
-- SELECT
-- product_name,
-- avg_rating,
-- CASE
-- WHEN age BETWEEN 18 AND 30 THEN '18-30'
-- WHEN age BETWEEN 31 AND 50 THEN '31-50'
-- ELSE '51+'
-- END AS age_group,
-- AVG(usage_duration_in_months) AS avg_usage_months,
-- AVG(severity) AS avg_severity,
-- SUM(recommendation_count) AS total_recommendations
-- FROM ProductRecommendations
-- GROUP BY product_name, age_group
-- )
-- SELECT
-- age_group,
-- product_name,
-- avg_rating,
-- ROUND(avg_usage_months, 2) AS avg_usage_months,
-- ROUND(avg_severity, 1) AS avg_severity,
-- total_recommendations
-- FROM AgeGroupStats
-- WHERE age_group = '51+' AND total_recommendations = (
-- SELECT MAX(total_recommendations)
-- FROM AgeGroupStats AS inner_ag
-- WHERE inner_ag.age_group = '51+'
-- )
-- ORDER BY total_recommendations DESC;
-- WITH CategoryEffectiveness AS (
-- SELECT
-- p.category,
-- pat.skin_condition,
-- COUNT(DISTINCT pr.patient_id) AS num_patients,
-- AVG(pr.usage_duration_in_months) AS average_usage_duration
-- FROM Product p
-- JOIN ProductRecommendation pr ON p.product_id = pr.product_id
-- JOIN Patient pat ON pr.patient_id = pat.patient_id
-- GROUP BY p.category, pat.skin_condition
-- )
-- SELECT
-- category,
-- skin_condition,
-- num_patients,
-- ROUND(average_usage_duration, 2) AS average_usage_duration
-- FROM CategoryEffectiveness
-- ORDER BY category, num_patients DESC, average_usage_duration DESC;
WITH DermatologistRecommendations AS (
SELECT
d.derm_id,
d.name AS dermatologist_name,
p.category,
COUNT(*) AS recommendations_count
FROM Dermatologist d
JOIN ProductRecommendation pr ON d.derm_id = pr.derm_id
JOIN Product p ON pr.product_id = p.product_id
GROUP BY d.derm_id, p.category
),
DiversityScore AS (
SELECT
derm_id,
dermatologist_name,
COUNT(DISTINCT category) AS diversity_score
FROM DermatologistRecommendations
GROUP BY derm_id
)
SELECT
d.dermatologist_name,
d.category,
d.recommendations_count,
ds.diversity_score
FROM DermatologistRecommendations d
JOIN DiversityScore ds ON d.derm_id = ds.derm_id
ORDER BY ds.diversity_score DESC, d.recommendations_count DESC;
To embed this program on your website, copy the following code and paste it into your website's HTML: