In sqlite

create table users ( email char(20), name char(16), pwd char(4), city char(15), gender char(1), primary key (email) ); create table products ( pid char(4), "This is the product id" descr char(20), "This is the product description" primary key (pid) ); create table sales ( sid char(4), "This is the sale id" lister char(20) not null, "This is the lister email" pid char(4), "This is the product id" edate date,"This is the end date of the sale" descr char(25), "This is the sale description" cond char(10), "This is the condition" rprice int, "This is the price" primary key (sid), foreign key (lister) references users, foreign key (pid) references products ); create table bids ( bid char(20), "This is the bid id" bidder char(20) not null, "This is the bidder email" sid char(4) not null, bdate date, "This is the bid date" amount float, "This is the bid amount" primary key (bid), foreign key (bidder) references users, foreign key (sid) references sales ); create table items ( sid char(4), "This is the item id" no int, pid char(4), descr char(25), primary key (sid,no), foreign key (sid) references sales on delete cascade, foreign key (pid) references products ); create table reviews ( reviewer char(20), "This is the email of the reviewer " reviewee char(20), "This is who is being reviewed" rating float, rtext char(20), "This is the review text" rdate date, "This is the review date" primary key (reviewer, reviewee), foreign key (reviewer) references users, foreign key (reviewee) references users ); create table previews ( rid int, pid char(4), reviewer char(20) not null, rating float, rtext char(20), rdate date, primary key (rid), foreign key (pid) references products, foreign key (reviewer) references users ); create table endorses ( rid int, endorser char(20), primary key (rid, endorser), foreign key (rid) references previews, foreign key (endorser) references users );

Question:

For each user who has had some bids, list the user email, the total number of distinct sales the user has had bids on, the number of winning bids, and the total dollar amount of the wining bids. The user has a winning bid if the sale is over (i.e., the end date is in the past), the user has the highest bid and the bid amount is not less than the reserved price. The result should include users who have no winning bids. Hint: subqueries can be used in the from clause.