Search code examples
sqlimpala

How to write an Impala query

I am trying to write a Impala query.

I have two tables - one containing holiday list from 2018 to 2022 and second where I have sales data with month start and end date. I have to write a query where I should be able to get a new column in sales table with holiday date (if my holiday date falls between start and end date then there should be that specific holiday date). Also, if there are two holidays coming in particular month then we have to mention both dates with '|' separator.

Holiday table -

enter image description here

Sales table -

enter image description here

Expected output -

enter image description here

I am using below query:

SELECT
    a.*,
    case when h.fest_date between a.month_start and a.month_end then h.fest_date
         else ''
    end as holiday_date
FROM sales a
JOIN holiday h
Solution

  • I think you are on right track. You need to use group_concat to concatenate and then a group by.

    SELECT a.prod, a.sales, a.start_date, a.end_date,
group_concat(holiday_date,"|") holiday_date  -- it will concat the holdays with a pipe seperator
FROM 
(SELECT a.prod, a.sales, a.start_date, a.end_date,
case when h.fest_date between a.month_start and a.month_end then h.fest_date end as holiday_date
FROM sales a
JOIN holiday h on h.fest_date between a.month_start and a.month_end
) a
group by a.prod, a.sales, a.start_date, a.end_date