Find the customer with the highest daily total order cost between 2019-02-01 to 2019-05-01. If customer had more than one order on a certain day, sum the order costs on daily basis. Output customer's first name, total cost of their items, and the date.
For simplicity, you can assume that every first name in the dataset is unique.
table name: customers
CREATE TABLE [dbo].[customers](
[id] [int] NOT NULL,
[first_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
[city] [varchar](50) NULL,
[address] [varchar](250) NULL,
[phone_number] [varchar](50) NULL,
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
table name: orders
CREATE TABLE [dbo].[orders](
[id] [int] NULL,
[cust_id] [int] NULL,
[order_date] [datetime] NULL,
[order_details] [varchar](250) NULL,
[total_order_cost] [int] NULL
) ON [PRIMARY]
GO
Solution:
select top 1 c.first_name, sum(total_order_cost) as total, o.order_date from customers as c
join [orders] as o on c.id = o.cust_id
where o.order_date between '2019-02-01' and '2019-05-01'
group by o.order_date,c.first_name
order by total desc
Output: