GlobalLogic Senior Software Developer Interview Questions and Answers

Use a common table expression (CTE) with ROW_NUMBER() function to delete duplicates from a table in SQL Server.

• Create a CTE with ROW_NUMBER() function to assign a unique row number to each row based on the duplicate column(s)

• Use the DELETE statement with the CTE to delete rows where the row number is greater than 1

ETL process involves extracting data from various sources, transforming it to fit the data warehouse schema, and loading it into the warehouse.

• Extract: Data is extracted from different sources such as databases, files, APIs, etc.

• Transform: Data is cleaned, filtered, aggregated, and transformed to match the data warehouse schema.

• Load: Transformed data is loaded into the data warehouse for analysis and reporting.

• Example: Extracting sales data from a CRM system, transforming it ...read more

Fact Table contains quantitative data and measures, while Dimension Table contains descriptive attributes.

• Fact Table contains numerical data that can be aggregated, such as sales revenue or quantity sold

• Dimension Table contains descriptive attributes for analysis, such as product name or customer details

• Fact Table typically has a many-to-one relationship with Dimension Table

• Fact Table is usually normalized, while Dimension Table is denormalized for faster query performance

INNER JOIN returns rows when there is at least one match in both tables, while OUTER JOIN returns all rows from both tables.

• INNER JOIN only returns rows that have matching values in both tables

• OUTER JOIN returns all rows from both tables, filling in NULLs for unmatched rows

• Types of OUTER JOIN include LEFT JOIN, RIGHT JOIN, and FULL JOIN

• Example: SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

• Example: SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.i...read more

A degenerate dimension is a dimension that consists of only one attribute or column.

• It is typically used when a dimension table has only one column and no other attributes.

• It is often denormalized and included directly in the fact table.

• Example: a date dimension with only a date column can be considered a degenerate dimension.

OLAP is used for analyzing historical data for decision-making, while OLTP is used for managing real-time transactional data.

• OLAP stands for Online Analytical Processing, used for complex queries and data analysis.

• OLTP stands for Online Transaction Processing, used for managing real-time transactional data.

• OLAP databases are optimized for read-heavy workloads, while OLTP databases are optimized for write-heavy workloads.

• OLAP databases typically have denormalized data structur...read more

CTE stands for Common Table Expressions, used to create temporary result sets within a SQL query.

• CTE is defined using the WITH keyword in SQL.

• It helps in simplifying complex queries by breaking them into smaller, more manageable parts.

• CTEs can reference themselves recursively, making them useful for hierarchical data.

• Example: WITH cte AS (SELECT * FROM table_name) SELECT * FROM cte;