SELECT date_id, make_name, COUNT(DISTINCT lead_id) AS unique_leads, COUNT(DISTINCT partner_id) AS unique_partners
FROM DailySales group by date_id, make_name;

select event_day as day, emp_id, (sum(out_time) - sum(in_time)) as total_time
from employees group by emp_id, day;

SELECT employee_id, (
CASE
    WHEN employee_id%2=1 AND name NOT LIKE 'M%' THEN salary
    ELSE 0
END
) AS bonus FROM employees;

select stock_name, sum(
case
    when operation = 'Buy' then -price
    else price
end
) as capital_gain_loss from stocks
group by stock_name

class Solution {
    public int findDuplicate(int[] nums) {
        Set<Integer> set = new HashSet<>();
        for(int n: nums){
            if(!set.contains(n))
                set.add(n);
            else
                return n;
        }
        
        return -1;
    }
}

class Solution {
    public boolean searchMatrix(int[][] matrix, int target) {
        int r=matrix.length, c = matrix[0].length;
        int start=0, end=r*c-1, mid=(start+end)/2;
        
        while(start<=end){
            if(matrix[mid/c][mid%c]>target)
                end=mid-1;
            else if(matrix[mid/c][mid%c]<target)
                start=mid+1;
            else
                return true;
            
            mid=(start+end)/2;
        }
        
        return false;
    }
}

select
    u.name, sum(t.amount) as balance
from users u
join transactions t on u.account=t.account
group by t.account 
having balance>10000;

class Solution {
    public boolean checkStraightLine(int[][] coordinates) {
        int x0 = coordinates[0][0], y0 = coordinates[0][1], 
            x1 = coordinates[1][0], y1 = coordinates[1][1];
        int dx = x1 - x0, dy = y1 - y0;
        for (int[] co : coordinates) {
            int x = co[0], y = co[1];
            if (dx * (y - y1) != dy * (x - x1))
                return false;
        }
        return true;
    }
}

class Solution {
    public int sumOfLeftLeaves(TreeNode root) {
        int res=0;
        if(root == null) return 0;
        if(root.left != null){
            if(root.left.left == null && root.left.right == null) res += root.left.val;
            else res += sumOfLeftLeaves(root.left);
        }
        
        res+=sumOfLeftLeaves(root.right);
        
        return res;
    }
}

class Solution {
    List<Integer> list = new ArrayList<>();
    public List<Integer> preorder(Node root) {
        if(root==null) return list;
        
        list.add(root.val);
        for(int i=0; i<root.children.size(); i++){
            preorder(root.children.get(i));
        }
        
        return list;
    }
}

class Solution {
    public boolean isAlienSorted(String[] words, String order) {
        
        for(int i=0; i<words.length-1; i++){
            if(!check(words[i], words[i+1], order))
                return false;
        }
        
        return true;
    }
    
    public boolean check(String s1, String s2, String order){
        for(int i=0; i<Math.min(s1.length(), s2.length()); i++){
            if(order.indexOf(s1.charAt(i))<order.indexOf(s2.charAt(i)))
                return true;
            if(order.indexOf(s1.charAt(i))>order.indexOf(s2.charAt(i)))
                return false;
        }
        
        return s1.length()<=s2.length();
    }
}

class UndergroundSystem {
    Map<Integer, Pair<String, Integer>> checkInMap = new HashMap<>();
    Map<String, Pair<Integer, Integer>> timeMap = new HashMap<>();
    public UndergroundSystem() {}
    public void checkIn(int id, String stationName, int t) {
        checkInMap.put(id, new Pair<>(stationName, t));
    }
    public void checkOut(int id, String stationName, int t) {
        Pair<String, Integer> checkIn = checkInMap.get(id);
        String stations = checkIn.getKey()+"-"+stationName;
        Pair<Integer, Integer> times = timeMap.getOrDefault(stations, new Pair<>(0, 0));
        int sum=(t-checkIn.getValue())+times.getKey();
        timeMap.put(stations, new Pair<>(sum, times.getValue()+1));
    }
    public double getAverageTime(String startStation, String endStation) {
        Pair<Integer, Integer> times = timeMap.get(startStation+"-"+endStation);
        return (double)times.getKey()/times.getValue();
    }
}

class Solution {
    public int maxDepth(TreeNode root) {
        if(root==null){
            return 0;
        }
        return 1+Math.max(maxDepth(root.left),maxDepth(root.right));
    }
}

class Solution {
    public int[] sortByBits(int[] arr) {
        int n=arr.length;
        for (int i = 0; i < n-1; i++){
            for (int j = 0; j < n-i-1; j++){
                if (func(arr[j]) > func(arr[j+1]) || (arr[j] > arr[j+1] && func(arr[j]) == func(arr[j+1])))
                {
                    int temp = arr[j];
                    arr[j] = arr[j+1];
                    arr[j+1] = temp;
                }
            }
        }
        return arr;
    }
    public int func(int n) {
        int count=0;
        while(n!=0){
            if((n&1)==1)
            count++;
            n=n>>>1;
        }
        return count;
    }
}

class Cashier {
    int count=0, order;
    double discountProduct;
    Map<Integer, Integer> map = new HashMap<>();
    
    public Cashier(int n, int discount, int[] products, int[] prices) {
        this.order=n;
        this.discountProduct=1-(double)discount/100;
        for(int i=0; i<prices.length; i++){
            map.put(products[i], prices[i]);
        }
    }
    
    public double getBill(int[] product, int[] amount) {
        count++;
        int total=0;
        for(int i=0; i<amount.length; i++){
            total+=map.get(product[i])*amount[i];
        }
        
        return count%order==0? total*discountProduct : total;
    }
}

class Bank {
    long [] balance;
    public Bank(long[] balance) {this.balance=balance;}
    
    public boolean transfer(int account1, int account2, long money) {
        if(account1>balance.length || account2>balance.length || balance[--account1]<money)
            return false;
        
        balance[account1]-=money;
        balance[account2-1]+=money;
        return true;
    }
    
    public boolean deposit(int account, long money) {
        if(account>balance.length)
            return false;
        balance[account-1]+=money;
        return true;
    }
    
    public boolean withdraw(int account, long money) {
        if(account>balance.length || balance[--account]<money)
            return false;
        balance[account]-=money;
        return true;
    }
}

select product_id, 'store1' as store, store1 as price from products where store1 is not null
union
select product_id, 'store2' as store, store2 as price from products where store2 is not null
union
select product_id, 'store3' as store, store3 as price from products where store3 is not null

class Solution {
    public int timeRequiredToBuy(int[] tickets, int k) {
        int time=0;
        while(tickets[k]!=0){
            for(int i=0; i<tickets.length; i++){
                if(tickets[i]-- > 0) time++;
                if(tickets[k]==0) break;
            }
        }
        
        return time;
    }
}