| 1 | -- =============================================================
|
|---|
| 2 | -- dml.sql – Performance-testing seed data for Shifter
|
|---|
| 3 | -- Domain: professional business education (sales, marketing,
|
|---|
| 4 | -- finance, leadership, HR, operations, strategy, etc.)
|
|---|
| 5 | -- PostgreSQL only. Uses generate_series(); no literal row lists.
|
|---|
| 6 | --
|
|---|
| 7 | -- NOTES:
|
|---|
| 8 | -- 1. Sequence names follow Hibernate 6 / Spring Boot 3 defaults
|
|---|
| 9 | -- ({table}_seq). Adjust setval() calls if yours differ.
|
|---|
| 10 | -- 2. admin, expert, _user extend Account (MappedSuperclass).
|
|---|
| 11 | -- They may share one sequence (account_seq). If so, merge
|
|---|
| 12 | -- the three setval() calls at the bottom into one.
|
|---|
| 13 | -- 3. @ElementCollection tables are included:
|
|---|
| 14 | -- course_translation_what_will_be_learned
|
|---|
| 15 | -- learning_path_translation_learning_outcomes
|
|---|
| 16 | -- 4. Estimated total rows: ~90 million.
|
|---|
| 17 | -- Expected run time: 20-60 min depending on hardware.
|
|---|
| 18 | --
|
|---|
| 19 | -- Usage: psql -U <user> -d <db> -f dml.sql
|
|---|
| 20 | -- =============================================================
|
|---|
| 21 |
|
|---|
| 22 | -- BEGIN;
|
|---|
| 23 |
|
|---|
| 24 | SET LOCAL work_mem = '256MB';
|
|---|
| 25 |
|
|---|
| 26 | -- =============================================================
|
|---|
| 27 | -- SHARED: language (2 rows)
|
|---|
| 28 | -- =============================================================
|
|---|
| 29 | INSERT INTO language (id, language_code, name, native_name) VALUES
|
|---|
| 30 | (1, 'EN', 'English', 'English'),
|
|---|
| 31 | (2, 'MK', 'Macedonian', 'Македонски')
|
|---|
| 32 | ON CONFLICT DO NOTHING;
|
|---|
| 33 |
|
|---|
| 34 | -- =============================================================
|
|---|
| 35 | -- IDENTITY: admin (10 rows, IDs 1-10)
|
|---|
| 36 | -- =============================================================
|
|---|
| 37 | INSERT INTO admin (id, name, email, login_provider, password_hash)
|
|---|
| 38 | SELECT
|
|---|
| 39 | s,
|
|---|
| 40 | 'Admin User ' || s,
|
|---|
| 41 | 'admin' || s || '@shifter.com',
|
|---|
| 42 | 'LOCAL',
|
|---|
| 43 | '$2a$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LPVDWgFcjFi'
|
|---|
| 44 | FROM generate_series(1, 10) s;
|
|---|
| 45 |
|
|---|
| 46 | -- =============================================================
|
|---|
| 47 | -- IDENTITY: expert (1 000 rows, IDs 11-1 010)
|
|---|
| 48 | -- Business educators, coaches, and consultants
|
|---|
| 49 | -- =============================================================
|
|---|
| 50 | INSERT INTO expert (id, name, email, login_provider, password_hash)
|
|---|
| 51 | SELECT
|
|---|
| 52 | 10 + s,
|
|---|
| 53 | (ARRAY[
|
|---|
| 54 | 'Margaret','Richard','Catherine','Thomas','Elizabeth',
|
|---|
| 55 | 'William','Patricia','Charles','Barbara','James',
|
|---|
| 56 | 'Susan','Robert','Jessica','David','Sarah',
|
|---|
| 57 | 'Michael','Karen','Daniel','Nancy','Mark'
|
|---|
| 58 | ])[1 + (s % 20)]
|
|---|
| 59 | || ' '
|
|---|
| 60 | || (ARRAY[
|
|---|
| 61 | 'Harrison','Mitchell','Campbell','Bennett','Thornton',
|
|---|
| 62 | 'Whitfield','Ashford','Caldwell','Pemberton','Langley',
|
|---|
| 63 | 'Harrington','Worthington','Blackwell','Sinclair','Fitzgerald',
|
|---|
| 64 | 'Montgomery','Chamberlain','Livingston','Wellington','Carrington'
|
|---|
| 65 | ])[1 + ((s / 20) % 20)],
|
|---|
| 66 | 'expert' || s || '@shifter.com',
|
|---|
| 67 | CASE WHEN s % 5 = 0 THEN 'GOOGLE' ELSE 'LOCAL' END,
|
|---|
| 68 | '$2a$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LPVDWgFcjFi'
|
|---|
| 69 | FROM generate_series(1, 1000) s;
|
|---|
| 70 |
|
|---|
| 71 | -- =============================================================
|
|---|
| 72 | -- IDENTITY: _user (2 000 000 rows, IDs 1 011-2 001 010)
|
|---|
| 73 | -- Business professionals across industries
|
|---|
| 74 | -- =============================================================
|
|---|
| 75 | INSERT INTO _user (
|
|---|
| 76 | id, name, email, login_provider, password_hash,
|
|---|
| 77 | verified, profile_complete, deleted, used_free_consultation,
|
|---|
| 78 | company_size, work_position, points, created_at, updated_at
|
|---|
| 79 | )
|
|---|
| 80 | SELECT
|
|---|
| 81 | 1010 + s,
|
|---|
| 82 | (ARRAY[
|
|---|
| 83 | 'James','Emma','Oliver','Sophia','Liam','Ava','Noah','Isabella',
|
|---|
| 84 | 'Ethan','Mia','Lucas','Charlotte','Mason','Amelia','Logan','Harper',
|
|---|
| 85 | 'Elijah','Evelyn','Aiden','Abigail','Jackson','Elizabeth','Grayson',
|
|---|
| 86 | 'Scarlett','Sebastian','Victoria','Carter','Aria','Jayden','Grace'
|
|---|
| 87 | ])[1 + (s % 30)]
|
|---|
| 88 | || ' '
|
|---|
| 89 | || (ARRAY[
|
|---|
| 90 | 'Smith','Johnson','Williams','Brown','Jones','Garcia','Miller',
|
|---|
| 91 | 'Davis','Wilson','Moore','Taylor','Anderson','Thomas','Jackson',
|
|---|
| 92 | 'White','Harris','Martin','Thompson','Young','Lee','Perez',
|
|---|
| 93 | 'Robinson','Clark','Lewis','Hall','Allen','King','Wright',
|
|---|
| 94 | 'Scott','Baker'
|
|---|
| 95 | ])[1 + ((s / 30) % 30)],
|
|---|
| 96 | 'user' || s || '@example.com',
|
|---|
| 97 | CASE WHEN s % 8 = 0 THEN 'GOOGLE' ELSE 'LOCAL' END,
|
|---|
| 98 | '$2a$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LPVDWgFcjFi',
|
|---|
| 99 | (s % 10) < 8,
|
|---|
| 100 | (s % 5) < 3,
|
|---|
| 101 | (s % 200) = 0,
|
|---|
| 102 | (s % 7) = 0,
|
|---|
| 103 | (ARRAY['FREELANCE','MICRO','SMALL','MEDIUM','MID_MARKET','ENTERPRISE','OTHER'])[1 + (s % 7)],
|
|---|
| 104 | (ARRAY[
|
|---|
| 105 | 'Sales Representative','Account Executive','Marketing Manager',
|
|---|
| 106 | 'Brand Manager','Financial Analyst','Business Analyst',
|
|---|
| 107 | 'HR Manager','Talent Acquisition Specialist','Operations Manager',
|
|---|
| 108 | 'Supply Chain Coordinator','Chief Executive Officer','Chief Financial Officer',
|
|---|
| 109 | 'Chief Marketing Officer','Vice President of Sales','Director of Operations',
|
|---|
| 110 | 'Business Development Manager','Customer Success Manager','Product Manager',
|
|---|
| 111 | 'Strategy Consultant','Entrepreneur'
|
|---|
| 112 | ])[1 + (s % 20)],
|
|---|
| 113 | s % 500,
|
|---|
| 114 | NOW() - ((s % 1095) || ' days')::INTERVAL,
|
|---|
| 115 | NOW() - ((s % 90) || ' days')::INTERVAL
|
|---|
| 116 | FROM generate_series(1, 2000000) s;
|
|---|
| 117 |
|
|---|
| 118 | -- =============================================================
|
|---|
| 119 | -- AUTH: verification_token (100 000 rows)
|
|---|
| 120 | -- =============================================================
|
|---|
| 121 | INSERT INTO verification_token (token, user_id, created_at, expires_at)
|
|---|
| 122 | SELECT
|
|---|
| 123 | gen_random_uuid(),
|
|---|
| 124 | 1010 + s,
|
|---|
| 125 | NOW() - ((s % 43200) || ' seconds')::INTERVAL,
|
|---|
| 126 | NOW() - ((s % 43200) || ' seconds')::INTERVAL + INTERVAL '30 minutes'
|
|---|
| 127 | FROM generate_series(1, 100000) s;
|
|---|
| 128 |
|
|---|
| 129 | -- =============================================================
|
|---|
| 130 | -- CATALOG: topic (500 rows)
|
|---|
| 131 | -- Business and professional development topic areas
|
|---|
| 132 | -- =============================================================
|
|---|
| 133 | INSERT INTO topic (id, slug)
|
|---|
| 134 | SELECT
|
|---|
| 135 | s,
|
|---|
| 136 | LOWER(REPLACE(
|
|---|
| 137 | (ARRAY[
|
|---|
| 138 | 'Sales','Digital Marketing','Brand Management','Content Marketing',
|
|---|
| 139 | 'Social Media Marketing','Email Marketing','Public Relations',
|
|---|
| 140 | 'Corporate Finance','Financial Planning','Investment Strategy',
|
|---|
| 141 | 'Accounting','Tax Planning','Risk Management','Leadership',
|
|---|
| 142 | 'Executive Management','Team Building','Conflict Resolution',
|
|---|
| 143 | 'Negotiation','Business Strategy','Entrepreneurship',
|
|---|
| 144 | 'Human Resources','Talent Management','Organisational Development',
|
|---|
| 145 | 'Operations Management','Supply Chain','Procurement',
|
|---|
| 146 | 'Customer Service','Customer Experience','Business Communication',
|
|---|
| 147 | 'Presentation Skills','Project Management','Change Management',
|
|---|
| 148 | 'Business Law','Compliance','Corporate Governance',
|
|---|
| 149 | 'Data-Driven Marketing','Market Research','Pricing Strategy',
|
|---|
| 150 | 'Retail Management','E-Commerce','Business Development',
|
|---|
| 151 | 'Mergers and Acquisitions','Private Equity','Venture Capital',
|
|---|
| 152 | 'Real Estate Business','Insurance','Business Analytics',
|
|---|
| 153 | 'Sustainability','Corporate Social Responsibility','Strategic Partnerships'
|
|---|
| 154 | ])[1 + ((s-1) % 50)],
|
|---|
| 155 | ' ', '-')) || '-' || ((s-1)/50 + 1)
|
|---|
| 156 | FROM generate_series(1, 500) s;
|
|---|
| 157 |
|
|---|
| 158 | -- topic_translation (1 000 rows – 2 per topic)
|
|---|
| 159 | INSERT INTO topic_translation (id, name, description, created_at, topic_id, language_id)
|
|---|
| 160 | SELECT
|
|---|
| 161 | s,
|
|---|
| 162 | (ARRAY[
|
|---|
| 163 | 'Sales','Digital Marketing','Brand Management','Content Marketing',
|
|---|
| 164 | 'Social Media Marketing','Email Marketing','Public Relations',
|
|---|
| 165 | 'Corporate Finance','Financial Planning','Investment Strategy',
|
|---|
| 166 | 'Accounting','Tax Planning','Risk Management','Leadership',
|
|---|
| 167 | 'Executive Management','Team Building','Conflict Resolution',
|
|---|
| 168 | 'Negotiation','Business Strategy','Entrepreneurship',
|
|---|
| 169 | 'Human Resources','Talent Management','Organisational Development',
|
|---|
| 170 | 'Operations Management','Supply Chain','Procurement',
|
|---|
| 171 | 'Customer Service','Customer Experience','Business Communication',
|
|---|
| 172 | 'Presentation Skills','Project Management','Change Management',
|
|---|
| 173 | 'Business Law','Compliance','Corporate Governance',
|
|---|
| 174 | 'Data-Driven Marketing','Market Research','Pricing Strategy',
|
|---|
| 175 | 'Retail Management','E-Commerce','Business Development',
|
|---|
| 176 | 'Mergers and Acquisitions','Private Equity','Venture Capital',
|
|---|
| 177 | 'Real Estate Business','Insurance','Business Analytics',
|
|---|
| 178 | 'Sustainability','Corporate Social Responsibility','Strategic Partnerships'
|
|---|
| 179 | ])[1 + (((s-1)/2) % 50)],
|
|---|
| 180 | 'Develop your expertise in '
|
|---|
| 181 | || LOWER((ARRAY[
|
|---|
| 182 | 'Sales','Digital Marketing','Brand Management','Content Marketing',
|
|---|
| 183 | 'Social Media Marketing','Email Marketing','Public Relations',
|
|---|
| 184 | 'Corporate Finance','Financial Planning','Investment Strategy',
|
|---|
| 185 | 'Accounting','Tax Planning','Risk Management','Leadership',
|
|---|
| 186 | 'Executive Management','Team Building','Conflict Resolution',
|
|---|
| 187 | 'Negotiation','Business Strategy','Entrepreneurship',
|
|---|
| 188 | 'Human Resources','Talent Management','Organisational Development',
|
|---|
| 189 | 'Operations Management','Supply Chain','Procurement',
|
|---|
| 190 | 'Customer Service','Customer Experience','Business Communication',
|
|---|
| 191 | 'Presentation Skills','Project Management','Change Management',
|
|---|
| 192 | 'Business Law','Compliance','Corporate Governance',
|
|---|
| 193 | 'Data-Driven Marketing','Market Research','Pricing Strategy',
|
|---|
| 194 | 'Retail Management','E-Commerce','Business Development',
|
|---|
| 195 | 'Mergers and Acquisitions','Private Equity','Venture Capital',
|
|---|
| 196 | 'Real Estate Business','Insurance','Business Analytics',
|
|---|
| 197 | 'Sustainability','Corporate Social Responsibility','Strategic Partnerships'
|
|---|
| 198 | ])[1 + (((s-1)/2) % 50)])
|
|---|
| 199 | || ' with courses taught by seasoned industry practitioners.',
|
|---|
| 200 | NOW() - ((s % 500) || ' days')::INTERVAL,
|
|---|
| 201 | (s + 1) / 2,
|
|---|
| 202 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END
|
|---|
| 203 | FROM generate_series(1, 1000) s;
|
|---|
| 204 |
|
|---|
| 205 | -- =============================================================
|
|---|
| 206 | -- CATALOG: skill (1 000 rows)
|
|---|
| 207 | -- Concrete professional and business skills
|
|---|
| 208 | -- =============================================================
|
|---|
| 209 | INSERT INTO skill (id, slug, show_in_radar)
|
|---|
| 210 | SELECT
|
|---|
| 211 | s,
|
|---|
| 212 | LOWER(REPLACE(
|
|---|
| 213 | (ARRAY[
|
|---|
| 214 | 'Cold Calling','B2B Sales','B2C Sales','Account Management',
|
|---|
| 215 | 'Sales Pipeline Management','CRM Software','Lead Generation',
|
|---|
| 216 | 'Sales Forecasting','Contract Negotiation','Closing Techniques',
|
|---|
| 217 | 'SEO & SEM','Pay-Per-Click Advertising','Social Media Strategy',
|
|---|
| 218 | 'Email Campaigns','Content Creation','Copywriting','Brand Positioning',
|
|---|
| 219 | 'Market Segmentation','Consumer Behaviour Analysis','Campaign Management',
|
|---|
| 220 | 'Financial Modelling','Budgeting & Forecasting','Cash Flow Management',
|
|---|
| 221 | 'Balance Sheet Analysis','Profit & Loss Reporting','Cost Reduction',
|
|---|
| 222 | 'Capital Allocation','Valuation Methods','Financial Risk Assessment',
|
|---|
| 223 | 'Audit & Compliance','Strategic Planning','Competitive Analysis',
|
|---|
| 224 | 'SWOT Analysis','Business Case Development','OKR Setting',
|
|---|
| 225 | 'KPI Tracking','Stakeholder Management','Executive Presentation',
|
|---|
| 226 | 'Active Listening','Persuasion & Influence','Conflict Mediation',
|
|---|
| 227 | 'Performance Management','Recruitment & Selection','Onboarding Design',
|
|---|
| 228 | 'Employee Engagement','Compensation & Benefits','Labour Law',
|
|---|
| 229 | 'Process Optimisation','Lean Management','Six Sigma',
|
|---|
| 230 | 'Inventory Management','Logistics Coordination','Vendor Management',
|
|---|
| 231 | 'Customer Retention','Net Promoter Score','Service Recovery',
|
|---|
| 232 | 'Project Scheduling','Risk Register','Agile for Business',
|
|---|
| 233 | 'Change Communication','Organisational Culture','Business Ethics',
|
|---|
| 234 | 'GDPR Compliance','Corporate Reporting','ESG Strategy',
|
|---|
| 235 | 'E-Commerce Operations','Marketplace Strategy','Pricing Analytics',
|
|---|
| 236 | 'Revenue Management','P&L Ownership','Cross-Functional Leadership'
|
|---|
| 237 | ])[1 + ((s-1) % 70)],
|
|---|
| 238 | ' ', '-')) || '-v' || ((s-1)/70 + 1),
|
|---|
| 239 | (s % 3) < 2
|
|---|
| 240 | FROM generate_series(1, 1000) s;
|
|---|
| 241 |
|
|---|
| 242 | -- skill_translation (2 000 rows)
|
|---|
| 243 | INSERT INTO skill_translation (id, name, description, created_at, skill_id, language_id)
|
|---|
| 244 | SELECT
|
|---|
| 245 | s,
|
|---|
| 246 | (ARRAY[
|
|---|
| 247 | 'Cold Calling','B2B Sales','B2C Sales','Account Management',
|
|---|
| 248 | 'Sales Pipeline Management','CRM Software','Lead Generation',
|
|---|
| 249 | 'Sales Forecasting','Contract Negotiation','Closing Techniques',
|
|---|
| 250 | 'SEO & SEM','Pay-Per-Click Advertising','Social Media Strategy',
|
|---|
| 251 | 'Email Campaigns','Content Creation','Copywriting','Brand Positioning',
|
|---|
| 252 | 'Market Segmentation','Consumer Behaviour Analysis','Campaign Management',
|
|---|
| 253 | 'Financial Modelling','Budgeting & Forecasting','Cash Flow Management',
|
|---|
| 254 | 'Balance Sheet Analysis','Profit & Loss Reporting','Cost Reduction',
|
|---|
| 255 | 'Capital Allocation','Valuation Methods','Financial Risk Assessment',
|
|---|
| 256 | 'Audit & Compliance','Strategic Planning','Competitive Analysis',
|
|---|
| 257 | 'SWOT Analysis','Business Case Development','OKR Setting',
|
|---|
| 258 | 'KPI Tracking','Stakeholder Management','Executive Presentation',
|
|---|
| 259 | 'Active Listening','Persuasion & Influence','Conflict Mediation',
|
|---|
| 260 | 'Performance Management','Recruitment & Selection','Onboarding Design',
|
|---|
| 261 | 'Employee Engagement','Compensation & Benefits','Labour Law',
|
|---|
| 262 | 'Process Optimisation','Lean Management','Six Sigma',
|
|---|
| 263 | 'Inventory Management','Logistics Coordination','Vendor Management',
|
|---|
| 264 | 'Customer Retention','Net Promoter Score','Service Recovery',
|
|---|
| 265 | 'Project Scheduling','Risk Register','Agile for Business',
|
|---|
| 266 | 'Change Communication','Organisational Culture','Business Ethics',
|
|---|
| 267 | 'GDPR Compliance','Corporate Reporting','ESG Strategy',
|
|---|
| 268 | 'E-Commerce Operations','Marketplace Strategy','Pricing Analytics',
|
|---|
| 269 | 'Revenue Management','P&L Ownership','Cross-Functional Leadership'
|
|---|
| 270 | ])[1 + (((s-1)/2) % 70)],
|
|---|
| 271 | 'Strengthen your ability to '
|
|---|
| 272 | || LOWER((ARRAY[
|
|---|
| 273 | 'close more deals through disciplined cold outreach',
|
|---|
| 274 | 'manage complex B2B relationships and enterprise accounts',
|
|---|
| 275 | 'drive consumer sales with persuasive techniques',
|
|---|
| 276 | 'grow and retain a portfolio of key accounts',
|
|---|
| 277 | 'build and manage a predictable sales pipeline',
|
|---|
| 278 | 'leverage CRM tools to track and convert leads',
|
|---|
| 279 | 'generate qualified leads through inbound and outbound channels',
|
|---|
| 280 | 'produce accurate revenue forecasts to guide business planning',
|
|---|
| 281 | 'negotiate contracts that protect margin and build partnerships',
|
|---|
| 282 | 'apply proven closing techniques to win more business',
|
|---|
| 283 | 'drive organic and paid traffic through search engine strategies',
|
|---|
| 284 | 'design and optimise pay-per-click campaigns for maximum ROI',
|
|---|
| 285 | 'build a social media presence that converts followers to customers',
|
|---|
| 286 | 'craft email campaigns that nurture leads and drive conversions',
|
|---|
| 287 | 'produce compelling content that attracts and retains audiences',
|
|---|
| 288 | 'write persuasive copy for ads, landing pages, and emails',
|
|---|
| 289 | 'define and communicate a differentiated brand position',
|
|---|
| 290 | 'identify and target the most valuable market segments',
|
|---|
| 291 | 'analyse consumer behaviour to inform marketing decisions',
|
|---|
| 292 | 'plan, execute, and optimise end-to-end marketing campaigns',
|
|---|
| 293 | 'build sophisticated financial models to support decision-making',
|
|---|
| 294 | 'produce accurate budgets and rolling forecasts',
|
|---|
| 295 | 'manage cash flow to ensure business liquidity',
|
|---|
| 296 | 'read and interpret balance sheets with confidence',
|
|---|
| 297 | 'produce and analyse profit and loss statements',
|
|---|
| 298 | 'identify and implement cost-reduction initiatives',
|
|---|
| 299 | 'allocate capital effectively across competing priorities',
|
|---|
| 300 | 'value businesses and assets using established methodologies',
|
|---|
| 301 | 'identify, quantify, and mitigate financial risk',
|
|---|
| 302 | 'ensure compliance with accounting standards and audit requirements',
|
|---|
| 303 | 'develop long-term strategic plans aligned to company goals',
|
|---|
| 304 | 'conduct competitive analysis to identify market opportunities',
|
|---|
| 305 | 'apply SWOT analysis to evaluate strategic options',
|
|---|
| 306 | 'build compelling business cases to gain executive buy-in',
|
|---|
| 307 | 'set and cascade OKRs across the organisation',
|
|---|
| 308 | 'define, track, and act on key performance indicators',
|
|---|
| 309 | 'manage stakeholders across all organisational levels',
|
|---|
| 310 | 'deliver high-impact executive presentations with confidence',
|
|---|
| 311 | 'practice active listening to improve communication outcomes',
|
|---|
| 312 | 'influence decisions and behaviours through principled persuasion',
|
|---|
| 313 | 'mediate workplace conflicts constructively and fairly',
|
|---|
| 314 | 'set clear performance expectations and conduct effective reviews',
|
|---|
| 315 | 'attract and select top talent through structured hiring processes',
|
|---|
| 316 | 'design onboarding programmes that accelerate new-hire productivity',
|
|---|
| 317 | 'measure and improve employee engagement across the organisation',
|
|---|
| 318 | 'design fair, competitive compensation and benefits packages',
|
|---|
| 319 | 'apply labour law principles to HR practice and policy',
|
|---|
| 320 | 'map and optimise business processes to eliminate waste',
|
|---|
| 321 | 'apply lean management principles to improve operational efficiency',
|
|---|
| 322 | 'use Six Sigma methods to reduce defects and variation',
|
|---|
| 323 | 'manage inventory levels to balance cost and service levels',
|
|---|
| 324 | 'coordinate logistics and transportation to meet delivery targets',
|
|---|
| 325 | 'select and manage vendors to deliver quality and value',
|
|---|
| 326 | 'design programmes that improve customer retention and loyalty',
|
|---|
| 327 | 'measure customer satisfaction using Net Promoter Score methodology',
|
|---|
| 328 | 'recover effectively from service failures to retain customers',
|
|---|
| 329 | 'build and maintain project schedules that keep teams on track',
|
|---|
| 330 | 'identify and manage project risks before they become issues',
|
|---|
| 331 | 'apply Agile principles in non-technical business contexts',
|
|---|
| 332 | 'communicate change initiatives in ways that build commitment',
|
|---|
| 333 | 'shape organisational culture to support strategic goals',
|
|---|
| 334 | 'apply ethical frameworks to complex business decisions',
|
|---|
| 335 | 'ensure data handling practices comply with GDPR requirements',
|
|---|
| 336 | 'produce clear, accurate corporate reports for stakeholders',
|
|---|
| 337 | 'develop and execute an ESG strategy that creates real value',
|
|---|
| 338 | 'run efficient e-commerce operations at scale',
|
|---|
| 339 | 'build a profitable presence on digital marketplaces',
|
|---|
| 340 | 'use pricing analytics to maximise revenue and margin',
|
|---|
| 341 | 'optimise revenue streams through dynamic pricing and yield management',
|
|---|
| 342 | 'own a P&L and make decisions that protect and grow profitability',
|
|---|
| 343 | 'lead cross-functional teams towards shared commercial objectives'
|
|---|
| 344 | ])[1 + (((s-1)/2) % 70)])
|
|---|
| 345 | || '.',
|
|---|
| 346 | NOW() - ((s % 365) || ' days')::INTERVAL,
|
|---|
| 347 | (s + 1) / 2,
|
|---|
| 348 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END
|
|---|
| 349 | FROM generate_series(1, 2000) s;
|
|---|
| 350 |
|
|---|
| 351 | -- =============================================================
|
|---|
| 352 | -- CATALOG: course (100 000 rows)
|
|---|
| 353 | -- =============================================================
|
|---|
| 354 | INSERT INTO course (id, image_url, color, difficulty, duration_minutes)
|
|---|
| 355 | SELECT
|
|---|
| 356 | s,
|
|---|
| 357 | 'https://cdn.shifter.com/courses/' || s || '/thumbnail.jpg',
|
|---|
| 358 | (ARRAY[
|
|---|
| 359 | '#1D4ED8','#7C3AED','#047857','#B45309','#B91C1C',
|
|---|
| 360 | '#0E7490','#4D7C0F','#C2410C','#BE185D','#0F766E'
|
|---|
| 361 | ])[1 + (s % 10)],
|
|---|
| 362 | (ARRAY['BEGINNER','INTERMEDIATE','ADVANCED','EXPERT'])[1 + (s % 4)],
|
|---|
| 363 | 45 + (s % 375)
|
|---|
| 364 | FROM generate_series(1, 100000) s;
|
|---|
| 365 |
|
|---|
| 366 | -- course_version (200 000 rows – 2 per course)
|
|---|
| 367 | INSERT INTO course_version (id, version_number, creation_date, active, course_id)
|
|---|
| 368 | SELECT
|
|---|
| 369 | s,
|
|---|
| 370 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END,
|
|---|
| 371 | CURRENT_DATE - ((CASE WHEN s % 2 = 1 THEN 365 ELSE 30 END + s % 100) || ' days')::INTERVAL,
|
|---|
| 372 | (s % 2 = 0),
|
|---|
| 373 | (s + 1) / 2
|
|---|
| 374 | FROM generate_series(1, 200000) s;
|
|---|
| 375 |
|
|---|
| 376 | -- course_price (100 000 rows – 1 per course)
|
|---|
| 377 | INSERT INTO course_price (
|
|---|
| 378 | id, amount, active, discounted,
|
|---|
| 379 | discount_amount, discount_percentage, created_at, course_id
|
|---|
| 380 | )
|
|---|
| 381 | SELECT
|
|---|
| 382 | s,
|
|---|
| 383 | (ARRAY[29,39,49,59,69,79,89,99,109,129,149,179,199,249,299])[1 + (s % 15)]::DECIMAL,
|
|---|
| 384 | true,
|
|---|
| 385 | (s % 3 = 0),
|
|---|
| 386 | CASE WHEN s % 3 = 0 THEN (ARRAY[10,15,20,25,30,40])[1 + (s % 6)]::DECIMAL ELSE 0 END,
|
|---|
| 387 | CASE WHEN s % 3 = 0 THEN (ARRAY[10,15,20,25,30,40])[1 + (s % 6)]::DECIMAL ELSE 0 END,
|
|---|
| 388 | NOW() - ((s % 730) || ' days')::INTERVAL,
|
|---|
| 389 | s
|
|---|
| 390 | FROM generate_series(1, 100000) s;
|
|---|
| 391 |
|
|---|
| 392 | -- course_translation (200 000 rows – 2 per course)
|
|---|
| 393 | INSERT INTO course_translation (
|
|---|
| 394 | id, title_short, title, description_short, description, description_long,
|
|---|
| 395 | created_at, language_id, course_id
|
|---|
| 396 | )
|
|---|
| 397 | SELECT
|
|---|
| 398 | s,
|
|---|
| 399 | -- title_short
|
|---|
| 400 | (ARRAY[
|
|---|
| 401 | 'Sales Fundamentals','B2B Sales Mastery','Winning Negotiations',
|
|---|
| 402 | 'Digital Marketing','Email Marketing Pro','Brand Strategy',
|
|---|
| 403 | 'Financial Planning','Corporate Finance','Management Accounting',
|
|---|
| 404 | 'Leadership Excellence','Executive Leadership','Team Management',
|
|---|
| 405 | 'HR Essentials','Talent Acquisition','Performance Management',
|
|---|
| 406 | 'Operations Management','Supply Chain','Lean & Six Sigma',
|
|---|
| 407 | 'Business Strategy','Entrepreneurship Bootcamp'
|
|---|
| 408 | ])[1 + (((s-1)/2) % 20)]
|
|---|
| 409 | || ' ' || ((s-1)/2 + 1),
|
|---|
| 410 | -- title
|
|---|
| 411 | 'The Complete '
|
|---|
| 412 | || (ARRAY[
|
|---|
| 413 | 'Sales Fundamentals','B2B Sales','Negotiation',
|
|---|
| 414 | 'Digital Marketing','Email Marketing','Brand Strategy',
|
|---|
| 415 | 'Financial Planning','Corporate Finance','Management Accounting',
|
|---|
| 416 | 'Leadership','Executive Leadership','Team Management',
|
|---|
| 417 | 'Human Resources','Talent Acquisition','Performance Management',
|
|---|
| 418 | 'Operations Management','Supply Chain Management','Lean & Six Sigma',
|
|---|
| 419 | 'Business Strategy','Entrepreneurship'
|
|---|
| 420 | ])[1 + (((s-1)/2) % 20)]
|
|---|
| 421 | || ' Course: From Principles to Practice',
|
|---|
| 422 | -- description_short
|
|---|
| 423 | 'Master '
|
|---|
| 424 | || LOWER((ARRAY[
|
|---|
| 425 | 'sales fundamentals','B2B sales','negotiation tactics',
|
|---|
| 426 | 'digital marketing','email marketing','brand strategy',
|
|---|
| 427 | 'financial planning','corporate finance','management accounting',
|
|---|
| 428 | 'leadership skills','executive leadership','team management',
|
|---|
| 429 | 'HR essentials','talent acquisition','performance management',
|
|---|
| 430 | 'operations management','supply chain management','lean and Six Sigma',
|
|---|
| 431 | 'business strategy','entrepreneurship'
|
|---|
| 432 | ])[1 + (((s-1)/2) % 20)])
|
|---|
| 433 | || ' with expert coaching and real-world case studies.',
|
|---|
| 434 | -- description
|
|---|
| 435 | 'This course gives you the frameworks, tools, and confidence to excel in '
|
|---|
| 436 | || LOWER((ARRAY[
|
|---|
| 437 | 'sales and revenue generation','B2B account management and pipeline building',
|
|---|
| 438 | 'high-stakes negotiations that protect your interests',
|
|---|
| 439 | 'end-to-end digital marketing strategy and execution',
|
|---|
| 440 | 'email marketing that nurtures leads and drives revenue',
|
|---|
| 441 | 'building and managing a powerful brand',
|
|---|
| 442 | 'personal and corporate financial planning',
|
|---|
| 443 | 'corporate finance decisions and capital allocation',
|
|---|
| 444 | 'management accounting and business performance reporting',
|
|---|
| 445 | 'leading teams and organisations with purpose and clarity',
|
|---|
| 446 | 'C-suite leadership and boardroom effectiveness',
|
|---|
| 447 | 'building, motivating, and retaining high-performing teams',
|
|---|
| 448 | 'HR policies, employment law, and people strategy',
|
|---|
| 449 | 'recruiting, selecting, and onboarding top talent',
|
|---|
| 450 | 'setting goals, reviewing performance, and developing people',
|
|---|
| 451 | 'operational efficiency and continuous improvement',
|
|---|
| 452 | 'supply chain design, sourcing, and logistics',
|
|---|
| 453 | 'lean management and Six Sigma quality methods',
|
|---|
| 454 | 'competitive strategy and strategic planning processes',
|
|---|
| 455 | 'launching and scaling a profitable business'
|
|---|
| 456 | ])[1 + (((s-1)/2) % 20)])
|
|---|
| 457 | || '. Taught by practitioners with decades of real industry experience.',
|
|---|
| 458 | -- description_long
|
|---|
| 459 | 'This in-depth programme is designed for professionals who want to move beyond theory and apply '
|
|---|
| 460 | || LOWER((ARRAY[
|
|---|
| 461 | 'proven sales methodologies','enterprise sales techniques',
|
|---|
| 462 | 'principled negotiation frameworks','integrated digital marketing strategies',
|
|---|
| 463 | 'data-driven email marketing approaches','strategic brand management principles',
|
|---|
| 464 | 'comprehensive financial planning tools','corporate finance techniques',
|
|---|
| 465 | 'management accounting and cost-control methods','transformational leadership practices',
|
|---|
| 466 | 'executive presence and strategic decision-making','people management and team dynamics',
|
|---|
| 467 | 'modern HR practices and employment relations','structured talent acquisition processes',
|
|---|
| 468 | 'evidence-based performance management systems','operational excellence frameworks',
|
|---|
| 469 | 'end-to-end supply chain management practices','lean and Six Sigma quality systems',
|
|---|
| 470 | 'rigorous business strategy frameworks','entrepreneurial growth models'
|
|---|
| 471 | ])[1 + (((s-1)/2) % 20)])
|
|---|
| 472 | || ' in their day-to-day roles. You will analyse real case studies, complete practical exercises, and earn a certificate of completion.',
|
|---|
| 473 | NOW() - ((s % 365) || ' days')::INTERVAL,
|
|---|
| 474 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END,
|
|---|
| 475 | (s + 1) / 2
|
|---|
| 476 | FROM generate_series(1, 200000) s;
|
|---|
| 477 |
|
|---|
| 478 | -- @ElementCollection: course_translation_what_will_be_learned
|
|---|
| 479 | -- (3 per translation × 200 000 = 600 000 rows)
|
|---|
| 480 | INSERT INTO course_translation_what_will_be_learned (
|
|---|
| 481 | course_translation_id, what_will_be_learned
|
|---|
| 482 | )
|
|---|
| 483 | SELECT
|
|---|
| 484 | ((s - 1) / 3) + 1,
|
|---|
| 485 | (ARRAY[
|
|---|
| 486 | 'Apply a repeatable, structured process to win more business',
|
|---|
| 487 | 'Analyse financial statements and make data-driven decisions',
|
|---|
| 488 | 'Lead difficult conversations and negotiations with confidence',
|
|---|
| 489 | 'Design and execute marketing campaigns that deliver measurable ROI',
|
|---|
| 490 | 'Build a high-performance culture within your team or organisation',
|
|---|
| 491 | 'Identify and exploit strategic opportunities before competitors do',
|
|---|
| 492 | 'Manage operations efficiently and eliminate costly waste',
|
|---|
| 493 | 'Attract, develop, and retain top talent in a competitive market',
|
|---|
| 494 | 'Communicate your ideas persuasively to any audience',
|
|---|
| 495 | 'Earn a certificate recognised by leading employers'
|
|---|
| 496 | ])[1 + (s % 10)]
|
|---|
| 497 | FROM generate_series(1, 600000) s;
|
|---|
| 498 |
|
|---|
| 499 | -- =============================================================
|
|---|
| 500 | -- CATALOG: course_module (1 000 000 rows – 5 per course_version)
|
|---|
| 501 | -- =============================================================
|
|---|
| 502 | INSERT INTO course_module (id, position, course_version_id)
|
|---|
| 503 | SELECT
|
|---|
| 504 | s,
|
|---|
| 505 | 1 + ((s - 1) % 5),
|
|---|
| 506 | (s - 1) / 5 + 1
|
|---|
| 507 | FROM generate_series(1, 1000000) s;
|
|---|
| 508 |
|
|---|
| 509 | -- course_module_translation (2 000 000 rows – 2 per module)
|
|---|
| 510 | INSERT INTO course_module_translation (id, title, created_at, language_id, course_module_id)
|
|---|
| 511 | SELECT
|
|---|
| 512 | s,
|
|---|
| 513 | (ARRAY[
|
|---|
| 514 | 'Foundations & Context','Core Frameworks','Applied Techniques',
|
|---|
| 515 | 'Real-World Case Studies','Assessment & Certification',
|
|---|
| 516 | 'Getting Started','Key Principles','Practical Skill-Building',
|
|---|
| 517 | 'Workplace Application','Review, Reflection & Next Steps'
|
|---|
| 518 | ])[1 + (((s-1)/2) % 10)],
|
|---|
| 519 | NOW() - ((s % 300) || ' days')::INTERVAL,
|
|---|
| 520 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END,
|
|---|
| 521 | (s + 1) / 2
|
|---|
| 522 | FROM generate_series(1, 2000000) s;
|
|---|
| 523 |
|
|---|
| 524 | -- =============================================================
|
|---|
| 525 | -- CATALOG: course_lecture (4 000 000 rows – 4 per module)
|
|---|
| 526 | -- =============================================================
|
|---|
| 527 | INSERT INTO course_lecture (id, duration_minutes, position, content_type, course_module_id)
|
|---|
| 528 | SELECT
|
|---|
| 529 | s,
|
|---|
| 530 | 5 + (s % 55),
|
|---|
| 531 | 1 + ((s - 1) % 4),
|
|---|
| 532 | (ARRAY['VIDEO','TEXT','FILE','QUIZ','TOOL'])[1 + (s % 5)],
|
|---|
| 533 | (s - 1) / 4 + 1
|
|---|
| 534 | FROM generate_series(1, 4000000) s;
|
|---|
| 535 |
|
|---|
| 536 | -- course_lecture_translation (8 000 000 rows – 2 per lecture)
|
|---|
| 537 | INSERT INTO course_lecture_translation (
|
|---|
| 538 | id, content_file_name, title, description, content_text,
|
|---|
| 539 | created_at, language_id, course_lecture_id
|
|---|
| 540 | )
|
|---|
| 541 | SELECT
|
|---|
| 542 | s,
|
|---|
| 543 | CASE WHEN (s % 5) IN (0,2) THEN NULL
|
|---|
| 544 | ELSE 'lecture-' || ((s+1)/2) || '.mp4'
|
|---|
| 545 | END,
|
|---|
| 546 | (ARRAY[
|
|---|
| 547 | 'Why This Matters: The Business Case',
|
|---|
| 548 | 'Core Concepts Every Professional Must Know',
|
|---|
| 549 | 'Frameworks Used by Industry Leaders',
|
|---|
| 550 | 'Step-by-Step Walkthrough with Examples',
|
|---|
| 551 | 'Common Pitfalls and How to Avoid Them',
|
|---|
| 552 | 'Applying This in Your Organisation',
|
|---|
| 553 | 'Real Case Study: What Worked and Why',
|
|---|
| 554 | 'Interactive Exercise: Practise the Skill',
|
|---|
| 555 | 'Checklist and Action Plan',
|
|---|
| 556 | 'Expert Interview: Lessons from the Field'
|
|---|
| 557 | ])[1 + (((s-1)/2) % 10)],
|
|---|
| 558 | 'In this lecture you will '
|
|---|
| 559 | || (ARRAY[
|
|---|
| 560 | 'understand why this skill is critical to career advancement',
|
|---|
| 561 | 'learn the key concepts and terminology used by practitioners',
|
|---|
| 562 | 'explore the frameworks that leading companies use in practice',
|
|---|
| 563 | 'follow a worked example from start to finish',
|
|---|
| 564 | 'identify the most common mistakes and how to sidestep them',
|
|---|
| 565 | 'discover how to apply this in your specific business context',
|
|---|
| 566 | 'analyse a real-world case study and draw actionable insights',
|
|---|
| 567 | 'practise the skill through a guided exercise with feedback',
|
|---|
| 568 | 'leave with a clear checklist and 30-day action plan',
|
|---|
| 569 | 'hear first-hand insights from an experienced industry leader'
|
|---|
| 570 | ])[1 + (((s-1)/2) % 10)]
|
|---|
| 571 | || '.',
|
|---|
| 572 | NULL,
|
|---|
| 573 | NOW() - ((s % 200) || ' days')::INTERVAL,
|
|---|
| 574 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END,
|
|---|
| 575 | (s + 1) / 2
|
|---|
| 576 | FROM generate_series(1, 8000000) s;
|
|---|
| 577 |
|
|---|
| 578 | -- =============================================================
|
|---|
| 579 | -- CATALOG: join tables
|
|---|
| 580 | -- =============================================================
|
|---|
| 581 |
|
|---|
| 582 | -- course_topic (~200 000 pairs – 2 per course)
|
|---|
| 583 | INSERT INTO course_topic (course_id, topic_id)
|
|---|
| 584 | SELECT s, 1 + (s % 500) FROM generate_series(1, 100000) s
|
|---|
| 585 | UNION
|
|---|
| 586 | SELECT s, 1 + ((s + 250) % 500) FROM generate_series(1, 100000) s
|
|---|
| 587 | ON CONFLICT DO NOTHING;
|
|---|
| 588 |
|
|---|
| 589 | -- course_skill (~200 000 pairs – 2 per course)
|
|---|
| 590 | INSERT INTO course_skill (course_id, skill_id)
|
|---|
| 591 | SELECT s, 1 + (s % 1000) FROM generate_series(1, 100000) s
|
|---|
| 592 | UNION
|
|---|
| 593 | SELECT s, 1 + ((s + 500) % 1000) FROM generate_series(1, 100000) s
|
|---|
| 594 | ON CONFLICT DO NOTHING;
|
|---|
| 595 |
|
|---|
| 596 | -- expert_course (~100 000 pairs)
|
|---|
| 597 | INSERT INTO expert_course (expert_id, course_id)
|
|---|
| 598 | SELECT DISTINCT
|
|---|
| 599 | 10 + (s % 1000) + 1,
|
|---|
| 600 | 1 + ((s * 97) % 100000)
|
|---|
| 601 | FROM generate_series(1, 100000) s
|
|---|
| 602 | ON CONFLICT DO NOTHING;
|
|---|
| 603 |
|
|---|
| 604 | -- related_course (200 000 rows)
|
|---|
| 605 | INSERT INTO related_course (id, similarity_score, calculated_at, course_id, related_course_id)
|
|---|
| 606 | SELECT
|
|---|
| 607 | s,
|
|---|
| 608 | (0.50 + (s % 50) * 0.01)::DECIMAL,
|
|---|
| 609 | NOW() - ((s % 30) || ' days')::INTERVAL,
|
|---|
| 610 | LEAST( 1 + (s % 100000), 1 + ((s * 37 + 13) % 100000)),
|
|---|
| 611 | GREATEST(1 + (s % 100000), 1 + ((s * 37 + 13) % 100000))
|
|---|
| 612 | FROM generate_series(1, 200000) s
|
|---|
| 613 | WHERE (s % 100000) <> ((s * 37 + 13) % 100000)
|
|---|
| 614 | ON CONFLICT DO NOTHING;
|
|---|
| 615 |
|
|---|
| 616 | -- =============================================================
|
|---|
| 617 | -- ASSESSMENT: quiz (100 000 rows – 1 per active course_version)
|
|---|
| 618 | -- Active course_version IDs are even: 2, 4, … 200 000
|
|---|
| 619 | -- =============================================================
|
|---|
| 620 | INSERT INTO quiz (
|
|---|
| 621 | id, type, passing_score, randomized, active, created_at,
|
|---|
| 622 | course_module_id, course_version_id
|
|---|
| 623 | )
|
|---|
| 624 | SELECT
|
|---|
| 625 | s,
|
|---|
| 626 | (ARRAY['PRE_DIAGNOSTIC','CHECKPOINT','FINAL'])[1 + (s % 3)],
|
|---|
| 627 | 60 + (s % 30),
|
|---|
| 628 | (s % 4 = 0),
|
|---|
| 629 | true,
|
|---|
| 630 | NOW() - ((s % 365) || ' days')::INTERVAL,
|
|---|
| 631 | NULL,
|
|---|
| 632 | s * 2
|
|---|
| 633 | FROM generate_series(1, 100000) s;
|
|---|
| 634 |
|
|---|
| 635 | -- quiz_translation (200 000 rows – 2 per quiz)
|
|---|
| 636 | INSERT INTO quiz_translation (id, title, description, created_at, language_id, quiz_id)
|
|---|
| 637 | SELECT
|
|---|
| 638 | s,
|
|---|
| 639 | (ARRAY[
|
|---|
| 640 | 'Pre-Programme Knowledge Check',
|
|---|
| 641 | 'Module Progress Assessment',
|
|---|
| 642 | 'Final Certification Exam',
|
|---|
| 643 | 'Practical Skills Evaluation',
|
|---|
| 644 | 'Business Acumen Assessment',
|
|---|
| 645 | 'Leadership Competency Review',
|
|---|
| 646 | 'Commercial Awareness Test',
|
|---|
| 647 | 'Strategic Thinking Evaluation',
|
|---|
| 648 | 'Functional Knowledge Quiz',
|
|---|
| 649 | 'Competency Benchmark Assessment'
|
|---|
| 650 | ])[1 + (((s-1)/2) % 10)],
|
|---|
| 651 | 'Test your understanding of the material covered and confirm you are ready to apply these skills in your professional role.',
|
|---|
| 652 | NOW() - ((s % 300) || ' days')::INTERVAL,
|
|---|
| 653 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END,
|
|---|
| 654 | (s + 1) / 2
|
|---|
| 655 | FROM generate_series(1, 200000) s;
|
|---|
| 656 |
|
|---|
| 657 | -- quiz_question (1 000 000 rows – 10 per quiz)
|
|---|
| 658 | INSERT INTO quiz_question (id, position, points, type, quiz_id)
|
|---|
| 659 | SELECT
|
|---|
| 660 | s,
|
|---|
| 661 | 1 + ((s - 1) % 10),
|
|---|
| 662 | (ARRAY[1,2,3,5])[1 + (s % 4)],
|
|---|
| 663 | (ARRAY['SINGLE_CHOICE','MULTIPLE_CHOICE','TRUE_FALSE'])[1 + (s % 3)],
|
|---|
| 664 | (s - 1) / 10 + 1
|
|---|
| 665 | FROM generate_series(1, 1000000) s;
|
|---|
| 666 |
|
|---|
| 667 | -- quiz_question_translation (2 000 000 rows – 2 per question)
|
|---|
| 668 | INSERT INTO quiz_question_translation (
|
|---|
| 669 | id, question_text, scenario, created_at, language_id, quiz_question_id
|
|---|
| 670 | )
|
|---|
| 671 | SELECT
|
|---|
| 672 | s,
|
|---|
| 673 | (ARRAY[
|
|---|
| 674 | 'Which approach is considered best practice when entering a new market segment?',
|
|---|
| 675 | 'A client objects that your price is too high. What is the most effective response?',
|
|---|
| 676 | 'True or False: A positive gross margin guarantees a profitable business.',
|
|---|
| 677 | 'Which of the following is the most reliable indicator of sales team performance?',
|
|---|
| 678 | 'A new competitor has entered your market with a lower price. What should you do first?',
|
|---|
| 679 | 'Which financial metric best measures the efficiency of working capital management?',
|
|---|
| 680 | 'Select all the characteristics of an effective performance review conversation.',
|
|---|
| 681 | 'Your team is resistant to a major organisational change. What is the first step?',
|
|---|
| 682 | 'Which procurement strategy is most appropriate for a single-source critical supplier?',
|
|---|
| 683 | 'A customer threatens to cancel their contract. What is the most appropriate first action?'
|
|---|
| 684 | ])[1 + (((s-1)/2) % 10)],
|
|---|
| 685 | CASE WHEN s % 7 = 0
|
|---|
| 686 | THEN 'A mid-size retail company with £50 million in annual revenue is reviewing its commercial strategy. '
|
|---|
| 687 | ELSE NULL
|
|---|
| 688 | END,
|
|---|
| 689 | NOW() - ((s % 200) || ' days')::INTERVAL,
|
|---|
| 690 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END,
|
|---|
| 691 | (s + 1) / 2
|
|---|
| 692 | FROM generate_series(1, 2000000) s;
|
|---|
| 693 |
|
|---|
| 694 | -- quiz_answer_option (4 000 000 rows – 4 per question)
|
|---|
| 695 | -- Position 0 mod 4 is always the correct option
|
|---|
| 696 | INSERT INTO quiz_answer_option (id, correct, quiz_question_id)
|
|---|
| 697 | SELECT
|
|---|
| 698 | s,
|
|---|
| 699 | (s % 4 = 0),
|
|---|
| 700 | (s - 1) / 4 + 1
|
|---|
| 701 | FROM generate_series(1, 4000000) s;
|
|---|
| 702 |
|
|---|
| 703 | -- quiz_answer_option_translation (8 000 000 rows – 2 per option)
|
|---|
| 704 | INSERT INTO quiz_answer_option_translation (
|
|---|
| 705 | id, answer_text, explanation, created_at, language_id, quiz_answer_option_id
|
|---|
| 706 | )
|
|---|
| 707 | SELECT
|
|---|
| 708 | s,
|
|---|
| 709 | (ARRAY[
|
|---|
| 710 | 'Conduct a thorough analysis of customer needs and competitive positioning before committing resources.',
|
|---|
| 711 | 'Launch immediately with the lowest price to capture market share quickly.',
|
|---|
| 712 | 'Replicate the market leader''s strategy to reduce execution risk.',
|
|---|
| 713 | 'Delay entry until the market is fully mature and risks are minimised.',
|
|---|
| 714 | 'Acknowledge the concern, quantify the value you deliver, and reinforce ROI.',
|
|---|
| 715 | 'Immediately offer a discount to avoid losing the deal.',
|
|---|
| 716 | 'Escalate to your manager so they can handle the price negotiation.',
|
|---|
| 717 | 'Agree with the customer and promise to match any competitor price.',
|
|---|
| 718 | 'False — a business can have a positive gross margin and still be unprofitable if overheads exceed it.',
|
|---|
| 719 | 'True — gross margin is the single most important profitability metric for any business.',
|
|---|
| 720 | 'Revenue attainment against target, alongside pipeline coverage and conversion rates.',
|
|---|
| 721 | 'The number of calls made per day, regardless of outcomes.',
|
|---|
| 722 | 'Analyse your own value proposition and differentiate on quality, service, or relationship.',
|
|---|
| 723 | 'Match the competitor''s price immediately to protect market share.',
|
|---|
| 724 | 'Exit the market segment rather than compete on price.',
|
|---|
| 725 | 'Ignore the competitor and hope customers remain loyal.',
|
|---|
| 726 | 'Cash conversion cycle — it measures how efficiently a company converts investment into cash flow.',
|
|---|
| 727 | 'Return on equity, because it reflects overall financial health.',
|
|---|
| 728 | 'EBITDA margin, because it excludes all non-operational costs.',
|
|---|
| 729 | 'Revenue growth rate, as it shows the business is expanding.'
|
|---|
| 730 | ])[1 + (((s-1)/2) % 20)],
|
|---|
| 731 | CASE WHEN (((s-1)/2) % 4) = 0
|
|---|
| 732 | THEN 'Correct. This approach reflects best practice as taught in the course and is supported by the research evidence presented.'
|
|---|
| 733 | ELSE 'Incorrect. While plausible, this option overlooks a critical factor covered in the course material. Review the relevant module before retaking the assessment.'
|
|---|
| 734 | END,
|
|---|
| 735 | NOW() - ((s % 180) || ' days')::INTERVAL,
|
|---|
| 736 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END,
|
|---|
| 737 | (s + 1) / 2
|
|---|
| 738 | FROM generate_series(1, 8000000) s;
|
|---|
| 739 |
|
|---|
| 740 | -- quiz_question_skill (1 000 000 rows – 1 per question)
|
|---|
| 741 | INSERT INTO quiz_question_skill (id, proficiency, weight, quiz_question_id, skill_id)
|
|---|
| 742 | SELECT
|
|---|
| 743 | s,
|
|---|
| 744 | (ARRAY['BEGINNER','INTERMEDIATE','ADVANCED','EXPERT'])[1 + (s % 4)],
|
|---|
| 745 | 1 + (s % 5),
|
|---|
| 746 | s,
|
|---|
| 747 | 1 + (s % 1000)
|
|---|
| 748 | FROM generate_series(1, 1000000) s;
|
|---|
| 749 |
|
|---|
| 750 | -- =============================================================
|
|---|
| 751 | -- LEARNING: enrollment (10 000 000 rows)
|
|---|
| 752 | -- =============================================================
|
|---|
| 753 | INSERT INTO enrollment (
|
|---|
| 754 | id, enrollment_status, enrollment_type, on_trial,
|
|---|
| 755 | enrollment_date, purchase_date, activation_date, completion_date,
|
|---|
| 756 | updated_at, course_version_id, user_id
|
|---|
| 757 | )
|
|---|
| 758 | SELECT
|
|---|
| 759 | s,
|
|---|
| 760 | CASE
|
|---|
| 761 | WHEN s % 10 < 2 THEN 'PENDING'
|
|---|
| 762 | WHEN s % 10 < 7 THEN 'ACTIVE'
|
|---|
| 763 | ELSE 'COMPLETED'
|
|---|
| 764 | END,
|
|---|
| 765 | (ARRAY['INDIVIDUAL','BUNDLE','LEARNING_PATH'])[1 + (s % 3)],
|
|---|
| 766 | (s % 15 = 0),
|
|---|
| 767 | CURRENT_DATE - ((s % 730) || ' days')::INTERVAL,
|
|---|
| 768 | CASE WHEN s % 10 >= 2
|
|---|
| 769 | THEN CURRENT_DATE - ((s % 720) || ' days')::INTERVAL ELSE NULL END,
|
|---|
| 770 | CASE WHEN s % 10 >= 2
|
|---|
| 771 | THEN CURRENT_DATE - ((s % 715) || ' days')::INTERVAL ELSE NULL END,
|
|---|
| 772 | CASE WHEN s % 10 >= 7
|
|---|
| 773 | THEN CURRENT_DATE - ((s % 300) || ' days')::INTERVAL ELSE NULL END,
|
|---|
| 774 | NOW() - ((s % 30) || ' days')::INTERVAL,
|
|---|
| 775 | 2 * (1 + (s % 100000)),
|
|---|
| 776 | 1010 + 1 + (s % 2000000)
|
|---|
| 777 | FROM generate_series(1, 10000000) s;
|
|---|
| 778 |
|
|---|
| 779 | -- =============================================================
|
|---|
| 780 | -- LEARNING: lecture_progress (12 000 000 rows)
|
|---|
| 781 | -- =============================================================
|
|---|
| 782 | INSERT INTO lecture_progress (id, completed, completed_at, enrollment_id, course_lecture_id)
|
|---|
| 783 | SELECT
|
|---|
| 784 | s,
|
|---|
| 785 | (s % 8) < 6,
|
|---|
| 786 | CASE WHEN (s % 8) < 6
|
|---|
| 787 | THEN NOW() - ((s % 365) || ' days')::INTERVAL
|
|---|
| 788 | ELSE NOW()
|
|---|
| 789 | END,
|
|---|
| 790 | 1 + (s % 10000000),
|
|---|
| 791 | 1 + (s % 4000000)
|
|---|
| 792 | FROM generate_series(1, 12000000) s;
|
|---|
| 793 |
|
|---|
| 794 | -- =============================================================
|
|---|
| 795 | -- ASSESSMENT: quiz_attempt (3 000 000 rows)
|
|---|
| 796 | -- =============================================================
|
|---|
| 797 | INSERT INTO quiz_attempt (
|
|---|
| 798 | id, attempt_number, started_at, completed_at, status,
|
|---|
| 799 | score, total_points, earned_points, passed, quiz_id, enrollment_id
|
|---|
| 800 | )
|
|---|
| 801 | SELECT
|
|---|
| 802 | s,
|
|---|
| 803 | 1 + (s % 3),
|
|---|
| 804 | NOW() - ((s % 365) || ' days')::INTERVAL,
|
|---|
| 805 | NOW() - ((s % 365) || ' days')::INTERVAL + INTERVAL '30 minutes',
|
|---|
| 806 | (ARRAY['PASSED','PASSED','FAILED','ABANDONED'])[1 + (s % 4)],
|
|---|
| 807 | 50 + (s % 50),
|
|---|
| 808 | 100,
|
|---|
| 809 | 50 + (s % 50),
|
|---|
| 810 | (50 + (s % 50)) >= 70,
|
|---|
| 811 | 1 + (s % 100000),
|
|---|
| 812 | 1 + (s % 10000000)
|
|---|
| 813 | FROM generate_series(1, 3000000) s;
|
|---|
| 814 |
|
|---|
| 815 | -- quiz_attempt_answer (6 000 000 rows – 2 per attempt)
|
|---|
| 816 | INSERT INTO quiz_attempt_answer (id, correct, quiz_question_id, quiz_attempt_id)
|
|---|
| 817 | SELECT
|
|---|
| 818 | s,
|
|---|
| 819 | (s % 3) < 2,
|
|---|
| 820 | 1 + (s % 1000000),
|
|---|
| 821 | (s - 1) / 2 + 1
|
|---|
| 822 | FROM generate_series(1, 6000000) s;
|
|---|
| 823 |
|
|---|
| 824 | -- quiz_attempt_answer_selected_options (~9 000 000 rows)
|
|---|
| 825 | INSERT INTO quiz_attempt_answer_selected_options (quiz_attempt_answer_id, quiz_answer_option_id)
|
|---|
| 826 | SELECT s, 1 + (s % 4000000)
|
|---|
| 827 | FROM generate_series(1, 6000000) s
|
|---|
| 828 | UNION ALL
|
|---|
| 829 | SELECT s, 1 + ((s + 2000000) % 4000000)
|
|---|
| 830 | FROM generate_series(1, 3000000) s;
|
|---|
| 831 |
|
|---|
| 832 | -- =============================================================
|
|---|
| 833 | -- LEARNING: user_skill (2 000 000 rows)
|
|---|
| 834 | -- =============================================================
|
|---|
| 835 | INSERT INTO user_skill (
|
|---|
| 836 | id, verified, proficiency, proficiency_score,
|
|---|
| 837 | created_at, updated_at, enrollment_id, skill_id, user_id
|
|---|
| 838 | )
|
|---|
| 839 | SELECT
|
|---|
| 840 | s,
|
|---|
| 841 | (s % 5) < 3,
|
|---|
| 842 | (ARRAY['BEGINNER','INTERMEDIATE','ADVANCED','EXPERT'])[1 + (s % 4)],
|
|---|
| 843 | 10 + (s % 90),
|
|---|
| 844 | NOW() - ((s % 365) || ' days')::INTERVAL,
|
|---|
| 845 | NOW() - ((s % 30) || ' days')::INTERVAL,
|
|---|
| 846 | 1 + (s % 10000000),
|
|---|
| 847 | 1 + (s % 1000),
|
|---|
| 848 | 1010 + 1 + (s % 2000000)
|
|---|
| 849 | FROM generate_series(1, 2000000) s;
|
|---|
| 850 |
|
|---|
| 851 | -- user_skill_snapshot (1 000 000 rows)
|
|---|
| 852 | INSERT INTO user_skill_snapshot (
|
|---|
| 853 | id, proficiency_at_time, proficiency_score_at_time,
|
|---|
| 854 | new_proficiency, new_proficiency_score, created_at,
|
|---|
| 855 | quiz_attempt_id, enrollment_id, user_skill_id
|
|---|
| 856 | )
|
|---|
| 857 | SELECT
|
|---|
| 858 | s,
|
|---|
| 859 | (ARRAY['BEGINNER','INTERMEDIATE','ADVANCED','EXPERT'])[1 + (s % 4)],
|
|---|
| 860 | 10 + (s % 60),
|
|---|
| 861 | (ARRAY['BEGINNER','INTERMEDIATE','ADVANCED','EXPERT'])[1 + ((s + 1) % 4)],
|
|---|
| 862 | 20 + (s % 70),
|
|---|
| 863 | NOW() - ((s % 300) || ' days')::INTERVAL,
|
|---|
| 864 | 1 + (s % 3000000),
|
|---|
| 865 | 1 + (s % 10000000),
|
|---|
| 866 | 1 + (s % 2000000)
|
|---|
| 867 | FROM generate_series(1, 1000000) s;
|
|---|
| 868 |
|
|---|
| 869 | -- =============================================================
|
|---|
| 870 | -- LEARNING: review (500 000 rows)
|
|---|
| 871 | -- Linked to enrollment IDs 1..500 000
|
|---|
| 872 | -- =============================================================
|
|---|
| 873 | INSERT INTO review (id, rating, comment, date, enrollment_id)
|
|---|
| 874 | SELECT
|
|---|
| 875 | s,
|
|---|
| 876 | 3 + (s % 3),
|
|---|
| 877 | (ARRAY[
|
|---|
| 878 | 'Excellent course — immediately applicable to my day-to-day role.',
|
|---|
| 879 | 'Incredibly well structured. The case studies made it come alive.',
|
|---|
| 880 | 'The instructor brought real credibility and practical insight.',
|
|---|
| 881 | 'Good content overall. A few modules could go deeper.',
|
|---|
| 882 | 'This course helped me land a promotion within three months.',
|
|---|
| 883 | 'Worth every penny. I have recommended it to my entire team.',
|
|---|
| 884 | 'Some sections felt a bit theoretical — more exercises would help.',
|
|---|
| 885 | NULL
|
|---|
| 886 | ])[1 + (s % 8)],
|
|---|
| 887 | CURRENT_DATE - ((s % 600) || ' days')::INTERVAL,
|
|---|
| 888 | s
|
|---|
| 889 | FROM generate_series(1, 500000) s;
|
|---|
| 890 |
|
|---|
| 891 | -- =============================================================
|
|---|
| 892 | -- LEARNING: certificate (300 000 rows)
|
|---|
| 893 | -- Linked to enrollment IDs 500 001..800 000
|
|---|
| 894 | -- =============================================================
|
|---|
| 895 | INSERT INTO certificate (
|
|---|
| 896 | id, issue_date, certificate_url, certificate_number, user_id, enrollment_id
|
|---|
| 897 | )
|
|---|
| 898 | SELECT
|
|---|
| 899 | s,
|
|---|
| 900 | CURRENT_DATE - ((s % 500) || ' days')::INTERVAL,
|
|---|
| 901 | 'https://cdn.shifter.com/certificates/CERT-' || LPAD(s::TEXT, 8, '0') || '.pdf',
|
|---|
| 902 | 'CERT-' || LPAD(s::TEXT, 8, '0'),
|
|---|
| 903 | 1010 + 1 + (s % 2000000),
|
|---|
| 904 | 500000 + s
|
|---|
| 905 | FROM generate_series(1, 300000) s;
|
|---|
| 906 |
|
|---|
| 907 | -- =============================================================
|
|---|
| 908 | -- COMMERCE: _order (1 000 000 rows)
|
|---|
| 909 | -- =============================================================
|
|---|
| 910 | INSERT INTO _order (id, status, created_at)
|
|---|
| 911 | SELECT
|
|---|
| 912 | s,
|
|---|
| 913 | (ARRAY['COMPLETED','COMPLETED','COMPLETED','PENDING','CANCELLED'])[1 + (s % 5)],
|
|---|
| 914 | NOW() - ((s % 730) || ' days')::INTERVAL
|
|---|
| 915 | FROM generate_series(1, 1000000) s;
|
|---|
| 916 |
|
|---|
| 917 | -- payment (1 000 000 rows)
|
|---|
| 918 | INSERT INTO payment (id, amount, date, method, status, order_id)
|
|---|
| 919 | SELECT
|
|---|
| 920 | s,
|
|---|
| 921 | (29 + (s % 270))::DOUBLE PRECISION,
|
|---|
| 922 | CURRENT_DATE - ((s % 730) || ' days')::INTERVAL,
|
|---|
| 923 | (ARRAY['CARD','PAYPAL','CASYS'])[1 + (s % 3)],
|
|---|
| 924 | (ARRAY['COMPLETED','COMPLETED','COMPLETED','PENDING','FAILED'])[1 + (s % 5)],
|
|---|
| 925 | s
|
|---|
| 926 | FROM generate_series(1, 1000000) s;
|
|---|
| 927 |
|
|---|
| 928 | -- order_details (1 000 000 rows)
|
|---|
| 929 | -- Linked to enrollment IDs 800 001..1 800 000
|
|---|
| 930 | INSERT INTO order_details (
|
|---|
| 931 | id, price, discount_amount, discount_percentage,
|
|---|
| 932 | created_at, order_id, enrollment_id, course_id
|
|---|
| 933 | )
|
|---|
| 934 | SELECT
|
|---|
| 935 | s,
|
|---|
| 936 | (29 + (s % 270))::DECIMAL,
|
|---|
| 937 | CASE WHEN s % 3 = 0 THEN (s % 40)::DECIMAL ELSE 0 END,
|
|---|
| 938 | CASE WHEN s % 3 = 0 THEN (10 + (s % 40))::DECIMAL ELSE 0 END,
|
|---|
| 939 | NOW() - ((s % 730) || ' days')::INTERVAL,
|
|---|
| 940 | s,
|
|---|
| 941 | 800000 + s,
|
|---|
| 942 | 1 + (s % 100000)
|
|---|
| 943 | FROM generate_series(1, 1000000) s;
|
|---|
| 944 |
|
|---|
| 945 | -- =============================================================
|
|---|
| 946 | -- CONSULTATION: meeting_email_reminder (200 000 rows)
|
|---|
| 947 | -- =============================================================
|
|---|
| 948 | INSERT INTO meeting_email_reminder (
|
|---|
| 949 | id, created_at, updated_at, meeting_at, scheduled_at,
|
|---|
| 950 | sent, status, meeting_link, user_id
|
|---|
| 951 | )
|
|---|
| 952 | SELECT
|
|---|
| 953 | s,
|
|---|
| 954 | NOW() - ((s % 365) || ' days')::INTERVAL,
|
|---|
| 955 | NOW() - ((s % 30) || ' days')::INTERVAL,
|
|---|
| 956 | NOW() + ((s % 60) || ' days')::INTERVAL,
|
|---|
| 957 | NOW() + ((s % 60) || ' days')::INTERVAL - INTERVAL '1 hour',
|
|---|
| 958 | (s % 3) < 2,
|
|---|
| 959 | (ARRAY['SENT','SENT','PENDING','FAILED'])[1 + (s % 4)],
|
|---|
| 960 | 'https://zoom.us/j/' || (1000000000 + s),
|
|---|
| 961 | 1010 + 1 + (s % 2000000)
|
|---|
| 962 | FROM generate_series(1, 200000) s;
|
|---|
| 963 |
|
|---|
| 964 | -- =============================================================
|
|---|
| 965 | -- COLLECTION: learning_path (10 000 rows)
|
|---|
| 966 | -- =============================================================
|
|---|
| 967 | INSERT INTO learning_path (
|
|---|
| 968 | id, type, slug, image_url, base_price, discounted,
|
|---|
| 969 | discount_amount, discount_percentage, estimated_duration_hours,
|
|---|
| 970 | difficulty, active, created_at
|
|---|
| 971 | )
|
|---|
| 972 | SELECT
|
|---|
| 973 | s,
|
|---|
| 974 | (ARRAY['SYSTEM_GENERATED','CURATED','CURATED','PERSONALIZED'])[1 + (s % 4)],
|
|---|
| 975 | 'learning-path-' || s,
|
|---|
| 976 | 'https://cdn.shifter.com/learning-paths/' || s || '/cover.jpg',
|
|---|
| 977 | (99 + (s % 300))::DECIMAL,
|
|---|
| 978 | (s % 3 = 0),
|
|---|
| 979 | CASE WHEN s % 3 = 0 THEN (15 + (s % 50))::DECIMAL ELSE 0 END,
|
|---|
| 980 | CASE WHEN s % 3 = 0 THEN (15 + (s % 30))::DECIMAL ELSE 0 END,
|
|---|
| 981 | 20 + (s % 80),
|
|---|
| 982 | (ARRAY['BEGINNER','INTERMEDIATE','ADVANCED','EXPERT'])[1 + (s % 4)],
|
|---|
| 983 | (s % 20) < 18,
|
|---|
| 984 | NOW() - ((s % 730) || ' days')::INTERVAL
|
|---|
| 985 | FROM generate_series(1, 10000) s;
|
|---|
| 986 |
|
|---|
| 987 | -- learning_path_translation (20 000 rows – 2 per path)
|
|---|
| 988 | INSERT INTO learning_path_translation (
|
|---|
| 989 | id, title, description, created_at, language_id, learning_path_id
|
|---|
| 990 | )
|
|---|
| 991 | SELECT
|
|---|
| 992 | s,
|
|---|
| 993 | (ARRAY[
|
|---|
| 994 | 'Complete Sales Professional Track',
|
|---|
| 995 | 'Digital Marketing Career Path',
|
|---|
| 996 | 'Finance for Business Leaders',
|
|---|
| 997 | 'Executive Leadership Programme',
|
|---|
| 998 | 'HR & People Management Track',
|
|---|
| 999 | 'Operations Excellence Journey',
|
|---|
| 1000 | 'Strategic Management Path',
|
|---|
| 1001 | 'Entrepreneurship & Growth Track',
|
|---|
| 1002 | 'Customer Success Career Path',
|
|---|
| 1003 | 'Commercial Acumen Development Programme'
|
|---|
| 1004 | ])[1 + (((s-1)/2) % 10)]
|
|---|
| 1005 | || ' ' || (((s-1)/2) / 10 + 1),
|
|---|
| 1006 | 'A comprehensive learning journey designed to take you from foundational knowledge to professional mastery through curated expert-led courses, case studies, and practical assessments.',
|
|---|
| 1007 | NOW() - ((s % 300) || ' days')::INTERVAL,
|
|---|
| 1008 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END,
|
|---|
| 1009 | (s + 1) / 2
|
|---|
| 1010 | FROM generate_series(1, 20000) s;
|
|---|
| 1011 |
|
|---|
| 1012 | -- @ElementCollection: learning_path_translation_learning_outcomes
|
|---|
| 1013 | -- (4 per translation × 20 000 = 80 000 rows)
|
|---|
| 1014 | INSERT INTO learning_path_translation_learning_outcomes (
|
|---|
| 1015 | learning_path_translation_id, learning_outcomes
|
|---|
| 1016 | )
|
|---|
| 1017 | SELECT
|
|---|
| 1018 | ((s - 1) / 4) + 1,
|
|---|
| 1019 | (ARRAY[
|
|---|
| 1020 | 'Build a complete professional skill set from foundations to advanced practice',
|
|---|
| 1021 | 'Apply proven frameworks and tools used by leading organisations worldwide',
|
|---|
| 1022 | 'Earn a professional certificate recognised by employers in your sector',
|
|---|
| 1023 | 'Lead teams, projects, and strategic initiatives with clarity and confidence',
|
|---|
| 1024 | 'Make evidence-based commercial decisions that improve business performance',
|
|---|
| 1025 | 'Communicate persuasively with stakeholders at every level of the organisation',
|
|---|
| 1026 | 'Analyse business challenges and design effective, practical solutions',
|
|---|
| 1027 | 'Advance your career with skills aligned to current employer demand'
|
|---|
| 1028 | ])[1 + (s % 8)]
|
|---|
| 1029 | FROM generate_series(1, 80000) s;
|
|---|
| 1030 |
|
|---|
| 1031 | -- curated_learning_path (5 000 rows)
|
|---|
| 1032 | INSERT INTO curated_learning_path (id, learning_path_id)
|
|---|
| 1033 | SELECT s, s * 2
|
|---|
| 1034 | FROM generate_series(1, 5000) s;
|
|---|
| 1035 |
|
|---|
| 1036 | -- expert_curated_learning_path join (15 000 rows)
|
|---|
| 1037 | INSERT INTO expert_curated_learning_path (expert_id, curated_learning_path_id)
|
|---|
| 1038 | SELECT DISTINCT
|
|---|
| 1039 | 10 + (s % 1000) + 1,
|
|---|
| 1040 | 1 + (s % 5000)
|
|---|
| 1041 | FROM generate_series(1, 15000) s
|
|---|
| 1042 | ON CONFLICT DO NOTHING;
|
|---|
| 1043 |
|
|---|
| 1044 | -- personalized_learning_path (100 000 rows)
|
|---|
| 1045 | INSERT INTO personalized_learning_path (
|
|---|
| 1046 | id, type, generated_reason, discounted,
|
|---|
| 1047 | added_discount_percent, added_discount_amount,
|
|---|
| 1048 | active, created_at, expires_at,
|
|---|
| 1049 | learning_path_id, source_learning_path_id, user_id
|
|---|
| 1050 | )
|
|---|
| 1051 | SELECT
|
|---|
| 1052 | s,
|
|---|
| 1053 | (ARRAY['SYSTEM_RECOMMENDATION','ADJUSTED_LEARNING_PATH'])[1 + (s % 2)],
|
|---|
| 1054 | (ARRAY['SKILL_GAP_DETECTED','CAREER_ADVANCEMENT',
|
|---|
| 1055 | 'ROLE_RELEVANT_SKILL','COURSE_ALREADY_ENROLLED'])[1 + (s % 4)],
|
|---|
| 1056 | (s % 4 = 0),
|
|---|
| 1057 | CASE WHEN s % 4 = 0 THEN (5 + (s % 20))::DECIMAL ELSE 0 END,
|
|---|
| 1058 | CASE WHEN s % 4 = 0 THEN (15 + (s % 35))::DECIMAL ELSE 0 END,
|
|---|
| 1059 | (s % 10) < 8,
|
|---|
| 1060 | NOW() - ((s % 180) || ' days')::INTERVAL,
|
|---|
| 1061 | NOW() + ((30 + s % 335) || ' days')::INTERVAL,
|
|---|
| 1062 | 1 + (s % 10000),
|
|---|
| 1063 | CASE WHEN s % 2 = 0 THEN 1 + (s % 5000) ELSE NULL END,
|
|---|
| 1064 | 1010 + 1 + (s % 2000000)
|
|---|
| 1065 | FROM generate_series(1, 100000) s;
|
|---|
| 1066 |
|
|---|
| 1067 | -- learning_path_course (60 000 rows – 6 courses per path)
|
|---|
| 1068 | INSERT INTO learning_path_course (id, sequence_order, learning_path_id, course_id)
|
|---|
| 1069 | SELECT
|
|---|
| 1070 | s,
|
|---|
| 1071 | 1 + ((s - 1) % 6),
|
|---|
| 1072 | (s - 1) / 6 + 1,
|
|---|
| 1073 | 1 + (s % 100000)
|
|---|
| 1074 | FROM generate_series(1, 60000) s;
|
|---|
| 1075 |
|
|---|
| 1076 | -- user_learning_path (300 000 rows)
|
|---|
| 1077 | INSERT INTO user_learning_path (
|
|---|
| 1078 | id, acquired_date, completed_date, status,
|
|---|
| 1079 | progress_percentage, updated_at, learning_path_id, user_id
|
|---|
| 1080 | )
|
|---|
| 1081 | SELECT
|
|---|
| 1082 | s,
|
|---|
| 1083 | CURRENT_DATE - ((s % 500) || ' days')::INTERVAL,
|
|---|
| 1084 | CASE WHEN s % 5 = 0
|
|---|
| 1085 | THEN CURRENT_DATE - ((s % 100) || ' days')::INTERVAL ELSE NULL END,
|
|---|
| 1086 | (ARRAY['NOT_STARTED','IN_PROGRESS','IN_PROGRESS','IN_PROGRESS','COMPLETED'])[1 + (s % 5)],
|
|---|
| 1087 | CASE WHEN s % 5 = 0 THEN 100 WHEN s % 5 = 1 THEN 0 ELSE 10 + (s % 90) END,
|
|---|
| 1088 | NOW() - ((s % 30) || ' days')::INTERVAL,
|
|---|
| 1089 | 1 + (s % 10000),
|
|---|
| 1090 | 1010 + 1 + (s % 2000000)
|
|---|
| 1091 | FROM generate_series(1, 300000) s;
|
|---|
| 1092 |
|
|---|
| 1093 | -- =============================================================
|
|---|
| 1094 | -- COLLECTION: bundle (5 000 rows)
|
|---|
| 1095 | -- =============================================================
|
|---|
| 1096 | INSERT INTO bundle (
|
|---|
| 1097 | id, type, slug, image_url, base_price,
|
|---|
| 1098 | discount_amount, discount_percentage, active, created_at, updated_at
|
|---|
| 1099 | )
|
|---|
| 1100 | SELECT
|
|---|
| 1101 | s,
|
|---|
| 1102 | (ARRAY['SYSTEM_GENERATED','CURATED','CURATED','PERSONALIZED'])[1 + (s % 4)],
|
|---|
| 1103 | 'bundle-' || s,
|
|---|
| 1104 | 'https://cdn.shifter.com/bundles/' || s || '/cover.jpg',
|
|---|
| 1105 | (79 + (s % 220))::DECIMAL,
|
|---|
| 1106 | CASE WHEN s % 3 = 0 THEN (10 + (s % 40))::DECIMAL ELSE 0 END,
|
|---|
| 1107 | CASE WHEN s % 3 = 0 THEN (10 + (s % 30))::DECIMAL ELSE 0 END,
|
|---|
| 1108 | (s % 15) < 13,
|
|---|
| 1109 | NOW() - ((s % 730) || ' days')::INTERVAL,
|
|---|
| 1110 | NOW() - ((s % 30) || ' days')::INTERVAL
|
|---|
| 1111 | FROM generate_series(1, 5000) s;
|
|---|
| 1112 |
|
|---|
| 1113 | -- bundle_translation (10 000 rows – 2 per bundle)
|
|---|
| 1114 | INSERT INTO bundle_translation (id, title, description, created_at, language_id, bundle_id)
|
|---|
| 1115 | SELECT
|
|---|
| 1116 | s,
|
|---|
| 1117 | (ARRAY[
|
|---|
| 1118 | 'Sales & Negotiation Bundle',
|
|---|
| 1119 | 'Marketing & Brand Essentials Pack',
|
|---|
| 1120 | 'Finance & Accounting Bundle',
|
|---|
| 1121 | 'Leadership & Management Pack',
|
|---|
| 1122 | 'HR & People Strategy Bundle',
|
|---|
| 1123 | 'Operations & Supply Chain Pack',
|
|---|
| 1124 | 'Strategy & Innovation Bundle',
|
|---|
| 1125 | 'Entrepreneurship Starter Kit',
|
|---|
| 1126 | 'Customer Success & Service Pack',
|
|---|
| 1127 | 'Commercial Skills Accelerator Bundle'
|
|---|
| 1128 | ])[1 + (((s-1)/2) % 10)]
|
|---|
| 1129 | || ' Vol.' || (((s-1)/2) / 10 + 1),
|
|---|
| 1130 | 'A carefully curated bundle of expert-led courses that equip you with the core skills to excel in this discipline.',
|
|---|
| 1131 | NOW() - ((s % 300) || ' days')::INTERVAL,
|
|---|
| 1132 | CASE WHEN s % 2 = 1 THEN 1 ELSE 2 END,
|
|---|
| 1133 | (s + 1) / 2
|
|---|
| 1134 | FROM generate_series(1, 10000) s;
|
|---|
| 1135 |
|
|---|
| 1136 | -- curated_bundle (2 500 rows)
|
|---|
| 1137 | INSERT INTO curated_bundle (id, bundle_id)
|
|---|
| 1138 | SELECT s, s * 2
|
|---|
| 1139 | FROM generate_series(1, 2500) s;
|
|---|
| 1140 |
|
|---|
| 1141 | -- expert_curated_bundle join (7 500 rows)
|
|---|
| 1142 | INSERT INTO expert_curated_bundle (expert_id, curated_bundle_id)
|
|---|
| 1143 | SELECT DISTINCT
|
|---|
| 1144 | 10 + (s % 1000) + 1,
|
|---|
| 1145 | 1 + (s % 2500)
|
|---|
| 1146 | FROM generate_series(1, 7500) s
|
|---|
| 1147 | ON CONFLICT DO NOTHING;
|
|---|
| 1148 |
|
|---|
| 1149 | -- bundle_course (25 000 rows – 5 per bundle)
|
|---|
| 1150 | INSERT INTO bundle_course (bundle_id, course_id)
|
|---|
| 1151 | SELECT
|
|---|
| 1152 | 1 + ((s - 1) / 5),
|
|---|
| 1153 | 1 + (s % 100000)
|
|---|
| 1154 | FROM generate_series(1, 25000) s
|
|---|
| 1155 | ON CONFLICT DO NOTHING;
|
|---|
| 1156 |
|
|---|
| 1157 | -- user_bundle (200 000 rows)
|
|---|
| 1158 | INSERT INTO user_bundle (id, acquired_date, bundle_id, user_id)
|
|---|
| 1159 | SELECT
|
|---|
| 1160 | s,
|
|---|
| 1161 | CURRENT_DATE - ((s % 500) || ' days')::INTERVAL,
|
|---|
| 1162 | 1 + (s % 5000),
|
|---|
| 1163 | 1010 + 1 + (s % 2000000)
|
|---|
| 1164 | FROM generate_series(1, 200000) s;
|
|---|
| 1165 |
|
|---|
| 1166 | -- personalized_bundle (50 000 rows)
|
|---|
| 1167 | INSERT INTO personalized_bundle (
|
|---|
| 1168 | id, type, generated_reason, discounted,
|
|---|
| 1169 | added_discount_percent, added_discount_amount,
|
|---|
| 1170 | active, created_at, expires_at,
|
|---|
| 1171 | bundle_id, source_bundle_id, user_id
|
|---|
| 1172 | )
|
|---|
| 1173 | SELECT
|
|---|
| 1174 | s,
|
|---|
| 1175 | (ARRAY['SYSTEM_RECOMMENDATION','ADJUSTED_BUNDLE'])[1 + (s % 2)],
|
|---|
| 1176 | (ARRAY['SKILL_GAP_DETECTED','CAREER_ADVANCEMENT',
|
|---|
| 1177 | 'ROLE_RELEVANT_SKILL','COURSE_ALREADY_ENROLLED'])[1 + (s % 4)],
|
|---|
| 1178 | (s % 4 = 0),
|
|---|
| 1179 | CASE WHEN s % 4 = 0 THEN (5 + (s % 15))::DECIMAL ELSE 0 END,
|
|---|
| 1180 | CASE WHEN s % 4 = 0 THEN (10 + (s % 30))::DECIMAL ELSE 0 END,
|
|---|
| 1181 | (s % 10) < 8,
|
|---|
| 1182 | NOW() - ((s % 180) || ' days')::INTERVAL,
|
|---|
| 1183 | NOW() + ((30 + s % 335) || ' days')::INTERVAL,
|
|---|
| 1184 | 1 + (s % 5000),
|
|---|
| 1185 | CASE WHEN s % 2 = 0 THEN 1 + (s % 2500) ELSE NULL END,
|
|---|
| 1186 | 1010 + 1 + (s % 2000000)
|
|---|
| 1187 | FROM generate_series(1, 50000) s;
|
|---|
| 1188 |
|
|---|
| 1189 | -- =============================================================
|
|---|
| 1190 | -- IDENTITY: user join tables
|
|---|
| 1191 | -- =============================================================
|
|---|
| 1192 |
|
|---|
| 1193 | -- user_favorite_course (500 000 rows)
|
|---|
| 1194 | INSERT INTO user_favorite_course (user_id, course_id)
|
|---|
| 1195 | SELECT DISTINCT
|
|---|
| 1196 | 1010 + 1 + (s % 2000000),
|
|---|
| 1197 | 1 + (s % 100000)
|
|---|
| 1198 | FROM generate_series(1, 500000) s
|
|---|
| 1199 | ON CONFLICT DO NOTHING;
|
|---|
| 1200 |
|
|---|
| 1201 | -- user_topic (400 000 rows)
|
|---|
| 1202 | INSERT INTO user_topic (user_id, topic_id)
|
|---|
| 1203 | SELECT DISTINCT
|
|---|
| 1204 | 1010 + 1 + (s % 2000000),
|
|---|
| 1205 | 1 + (s % 500)
|
|---|
| 1206 | FROM generate_series(1, 400000) s
|
|---|
| 1207 | ON CONFLICT DO NOTHING;
|
|---|
| 1208 |
|
|---|
| 1209 | -- =============================================================
|
|---|
| 1210 | -- CATALOG: course_activity_event (15 000 000 rows)
|
|---|
| 1211 | -- =============================================================
|
|---|
| 1212 | INSERT INTO course_activity_event (id, event_type, timestamp, course_id, user_id)
|
|---|
| 1213 | SELECT
|
|---|
| 1214 | s,
|
|---|
| 1215 | (ARRAY[
|
|---|
| 1216 | 'COURSE_VIEWED','COURSE_ENROLLED','COURSE_STARTED',
|
|---|
| 1217 | 'LESSON_STARTED','LESSON_COMPLETED','MODULE_COMPLETED',
|
|---|
| 1218 | 'VIDEO_WATCHED','QUIZ_ATTEMPTED','QUIZ_PASSED','QUIZ_FAILED',
|
|---|
| 1219 | 'COURSE_COMPLETED','COURSE_WISHLISTED','CERTIFICATE_DOWNLOADED',
|
|---|
| 1220 | 'COURSE_REVIEWED','COURSE_SHARED','RESOURCE_DOWNLOADED',
|
|---|
| 1221 | 'EXERCISE_SUBMITTED','COURSE_ABANDONED'
|
|---|
| 1222 | ])[1 + (s % 18)],
|
|---|
| 1223 | NOW() - ((s % 1095) || ' days')::INTERVAL,
|
|---|
| 1224 | 1 + (s % 100000),
|
|---|
| 1225 | 1010 + 1 + (s % 2000000)
|
|---|
| 1226 | FROM generate_series(1, 15000000) s;
|
|---|
| 1227 |
|
|---|
| 1228 | -- =============================================================
|
|---|
| 1229 | -- SEQUENCE RESETS
|
|---|
| 1230 | -- =============================================================
|
|---|
| 1231 | SELECT setval('language_seq', 10, true);
|
|---|
| 1232 | SELECT setval('admin_seq', 20, true);
|
|---|
| 1233 | SELECT setval('expert_seq', 1100, true);
|
|---|
| 1234 | SELECT setval('user_seq', 2100000, true);
|
|---|
| 1235 | SELECT setval('topic_seq', 600, true);
|
|---|
| 1236 | SELECT setval('topic_translation_seq', 1100, true);
|
|---|
| 1237 | SELECT setval('skill_seq', 1100, true);
|
|---|
| 1238 | SELECT setval('skill_translation_seq', 2100, true);
|
|---|
| 1239 | SELECT setval('course_seq', 110000, true);
|
|---|
| 1240 | SELECT setval('course_version_seq', 210000, true);
|
|---|
| 1241 | SELECT setval('course_price_seq', 110000, true);
|
|---|
| 1242 | SELECT setval('course_translation_seq', 210000, true);
|
|---|
| 1243 | SELECT setval('course_module_seq', 1100000, true);
|
|---|
| 1244 | SELECT setval('course_module_translation_seq', 2100000, true);
|
|---|
| 1245 | SELECT setval('course_lecture_seq', 4100000, true);
|
|---|
| 1246 | SELECT setval('course_lecture_translation_seq', 8100000, true);
|
|---|
| 1247 | SELECT setval('related_course_seq', 210000, true);
|
|---|
| 1248 | SELECT setval('quiz_seq', 110000, true);
|
|---|
| 1249 | SELECT setval('quiz_translation_seq', 210000, true);
|
|---|
| 1250 | SELECT setval('quiz_question_seq', 1100000, true);
|
|---|
| 1251 | SELECT setval('quiz_question_translation_seq', 2100000, true);
|
|---|
| 1252 | SELECT setval('quiz_answer_option_seq', 4100000, true);
|
|---|
| 1253 | SELECT setval('quiz_answer_option_translation_seq', 8100000, true);
|
|---|
| 1254 | SELECT setval('quiz_question_skill_seq', 1100000, true);
|
|---|
| 1255 | SELECT setval('quiz_attempt_seq', 3100000, true);
|
|---|
| 1256 | SELECT setval('quiz_attempt_answer_seq', 6100000, true);
|
|---|
| 1257 | SELECT setval('enrollment_seq', 10100000, true);
|
|---|
| 1258 | SELECT setval('lecture_progress_seq', 12100000, true);
|
|---|
| 1259 | SELECT setval('user_skill_seq', 2100000, true);
|
|---|
| 1260 | SELECT setval('user_skill_snapshot_seq', 1100000, true);
|
|---|
| 1261 | SELECT setval('review_seq', 600000, true);
|
|---|
| 1262 | SELECT setval('certificate_seq', 310000, true);
|
|---|
| 1263 | SELECT setval('order_seq', 1100000, true);
|
|---|
| 1264 | SELECT setval('payment_seq', 1100000, true);
|
|---|
| 1265 | SELECT setval('order_details_seq', 1100000, true);
|
|---|
| 1266 | SELECT setval('meeting_email_reminder_seq', 210000, true);
|
|---|
| 1267 | SELECT setval('learning_path_seq', 11000, true);
|
|---|
| 1268 | SELECT setval('learning_path_translation_seq', 21000, true);
|
|---|
| 1269 | SELECT setval('curated_learning_path_seq', 6000, true);
|
|---|
| 1270 | SELECT setval('personalized_learning_path_seq', 110000, true);
|
|---|
| 1271 | SELECT setval('learning_path_course_seq', 70000, true);
|
|---|
| 1272 | SELECT setval('user_learning_path_seq', 310000, true);
|
|---|
| 1273 | SELECT setval('bundle_seq', 6000, true);
|
|---|
| 1274 | SELECT setval('bundle_translation_seq', 11000, true);
|
|---|
| 1275 | SELECT setval('curated_bundle_seq', 3000, true);
|
|---|
| 1276 | SELECT setval('personalized_bundle_seq', 60000, true);
|
|---|
| 1277 | SELECT setval('user_bundle_seq', 210000, true);
|
|---|
| 1278 | SELECT setval('course_activity_event_seq', 15100000, true);
|
|---|
| 1279 |
|
|---|
| 1280 | -- COMMIT;
|
|---|
