How to display the oldest date for a unique user who has multiple dates in a database?

Let's say that my output looks like this (simplified example):

UserName	ProfileCreation	PurchasePrice	PurchaseDate
Alice	Dec 21 2019 6:00AM	120.00	Dec 21 2019 8:00AM
Alice	Dec 21 2019 6:00AM	90.00	Dec 25 2019 9:00AM
Alice	Dec 21 2019 6:00AM	150.00	Jan 02 2020 10:00AM
Bob	Jan 01 2020 9:00PM	50.00	Jan 03 2020 11:00PM
Bob	Jan 01 2020 9:00PM	70.00	Jan 07 2020 11:00PM

The code for this output would look like this, I guess (not that important):

SELECT
UserName, ProfileCreation, PurchasePrice, PurchaseDate
FROM Some_Random_Database

But my desired output should look like this:

UserName	ProfileCreation	PurchasePrice	FirstPurchaseDate	NumberOfPurchases	AvgOfPurchasePrice
Alice	Dec 21 2019	120.00	Dec 21 2019	3	120.00
Bob	Jan 01 2020	50.00	Jan 03 2020	2	60.00

Hopefully, it's understandable what my goal is - to have unique user with date of his/her oldest purchase and with some calculated metrics for all purchases. Price of the first purchase can stay, but it is not necessary.

I'm writing in SOQL dialect - Salesforce Marketing Cloud.

Obviously, I've got some ideas how to do some of the intended tweaks in my code, but I'd like to see a solution from any expert who is willing to show me the best way possible. I'm really just a noob :-)

I appreciate any help, guys!

Solution

Note: i know nothing about Salesforce Marketing Cloud, but...

There's few ways to achieve that:

#1 - standard sql

SELECT UserName, ProfileCreation
  , MIN(PurchaseDate) FirstPurchaseDate
  , COUNT(PurchasePrice) NoOfPurchases
  , AVG(PurchasePrice) AvgPurchasePrice
FROM Foo
GROUP BY UserName, ProfileCreation;

#2 - window functions

SELECT DISTINCT UserName, ProfileCreation
  , MIN(PurchaseDate) OVER(PARTITION BY UserName ORDER BY UserName) FirstPurchaseDate
  , COUNT(PurchasePrice) OVER(PARTITION BY UserName ORDER BY UserName) NoOfPurchases
  , AVG(PurchasePrice) OVER(PARTITION BY UserName ORDER BY UserName) AvgPurchasePrice
FROM Foo;