round 3

Python/pandas or datawarehouse/ dataModeling

Round 2

• extract 4th char from a string in a column

  • select substr(colmn_a,4,1) from table;

• how you can retrieve deleted data from 5 min old table

  • using time travel

    create table as t1_recover as

    select * from source_table at (offset ≥-5*60)

• create a table and load data

  create table if not exists table_new

  (col_1 datatype,

  col_2 datatype)

  #load data

  insert into table_new

  values (a,b),

  (c,d)

  #for copy data

  copy into table_new

  from @yourstage file_format=’csv’

• datatype to handle json and xml

  • variant with max size of 16 mb

• rolling wind

rolling window as per actual month (i.e if current month is apr then sum for all feb,mar and apr)

SELECT
    SaleDate,
    SalesAmount,
    SUM(SalesAmount) OVER (
        ORDER BY SaleDate
        RANGE BETWEEN INTERVAL '3 MONTH' PRECEDING AND CURRENT ROW
    ) AS RunningTotalLast3Months
FROM
    DailySales
ORDER BY
    SaleDate;

it will give the sum of previous last 3 rows including current

SELECT
    your_date_column,
    your_value_column,
    SUM(COALESCE(your_value_column, 0)) OVER (
        ORDER BY your_date_column
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS running_total_3_month_lag
FROM
    your_table;